PGSQL常見命令行與函數(shù)實(shí)例詳解
更新時(shí)間：2025年11月03日 12:06:40 作者：問道飛魚
本文詳細(xì)介紹了PostgreSQL常用命令行工具psql和內(nèi)置函數(shù)的操作方法,文章提供了大量實(shí)用的命令模板和函數(shù)示例,是PostgreSQL數(shù)據(jù)庫管理和開發(fā)的實(shí)用參考手冊(cè),適合數(shù)據(jù)庫管理員和開發(fā)人員快速查閱常用操作,感興趣的朋友跟隨小編一起看看吧
PostgreSQL 常用命令行與內(nèi)置函數(shù)詳解
一、PostgreSQL 命令行工具（psql）詳解
1. 數(shù)據(jù)庫連接與基本操作
（1）連接數(shù)據(jù)庫
# 基本連接（使用當(dāng)前系統(tǒng)用戶） psql # 連接指定數(shù)據(jù)庫 psql -d mydatabase # 連接指定用戶和數(shù)據(jù)庫 psql -U username -d mydatabase # 連接遠(yuǎn)程數(shù)據(jù)庫 psql -h hostname -p 5432 -U username -d mydatabase # 連接并執(zhí)行SQL文件 psql -d mydatabase -f script.sql # 連接并執(zhí)行單條命令 psql -d mydatabase -c "SELECT version();"
（2）連接參數(shù)說明
| 參數(shù) | 說明 | 示例 |
|---|---|---|
-d | 數(shù)據(jù)庫名 | -d postgres |
-U | 用戶名 | -U postgres |
-h | 主機(jī)地址 | -h localhost |
-p | 端口號(hào) | -p 5432 |
-W | 強(qiáng)制密碼提示 | -W |
-f | 執(zhí)行SQL文件 | -f init.sql |
-c | 執(zhí)行命令 | -c "SELECT 1" |
2. psql 內(nèi)部元命令（反斜杠命令）
（1）數(shù)據(jù)庫操作
-- 列出所有數(shù)據(jù)庫 \l \l+ -- 連接/切換數(shù)據(jù)庫 \c database_name \connect database_name -- 創(chuàng)建數(shù)據(jù)庫 CREATE DATABASE newdb; -- 刪除數(shù)據(jù)庫 DROP DATABASE olddb;
（2）表操作
-- 列出當(dāng)前數(shù)據(jù)庫所有表 \dt \dt+ -- 描述表結(jié)構(gòu) \d table_name \d+ table_name -- 列出所有索引 \di \di+ -- 列出所有序列 \ds \ds+
（3）模式操作
-- 列出所有模式 \dn \dn+ -- 設(shè)置搜索路徑 SET search_path TO schema1, schema2; -- 查看當(dāng)前搜索路徑 SHOW search_path;
（4）用戶和權(quán)限
-- 列出所有用戶/角色 \du \du+ -- 查看當(dāng)前用戶 SELECT current_user; -- 查看用戶權(quán)限 \dp table_name \z table_name
（5）信息查詢
-- 查看服務(wù)器版本 SELECT version(); -- 查看連接信息 \conninfo -- 查看當(dāng)前數(shù)據(jù)庫 SELECT current_database(); -- 查看當(dāng)前模式 SELECT current_schema();
（6）輸出和格式控制
-- 擴(kuò)展顯示（行轉(zhuǎn)列） \x on \x auto \x off -- 設(shè)置輸出格式 \a (對(duì)齊切換) \f (字段分隔符) -- 計(jì)時(shí)開關(guān) \timing on \timing off -- 輸出到文件 \o filename.txt \o
3. 實(shí)用元命令大全
| 命令 | 功能 | 示例 |
|---|---|---|
\? | 查看所有元命令幫助 | \? |
\h | SQL命令幫助 | \h CREATE TABLE |
\q | 退出psql | \q |
\! | 執(zhí)行系統(tǒng)命令 | \! ls -l |
\g | 再次執(zhí)行上次查詢 | \g |
\s | 查看命令歷史 | \s |
\e | 使用編輯器編輯查詢 | \e |
\i | 執(zhí)行外部SQL文件 | \i /path/to/file.sql |
\copy | 導(dǎo)入導(dǎo)出數(shù)據(jù) | \copy table TO 'file.csv' CSV |
二、PostgreSQL 內(nèi)置函數(shù)詳解
1. 數(shù)學(xué)函數(shù)
（1）基本數(shù)學(xué)運(yùn)算
-- 絕對(duì)值 SELECT abs(-15); -- 15 -- 四舍五入 SELECT round(3.14159, 2); -- 3.14 SELECT round(123.456); -- 123 -- 取整函數(shù) SELECT ceil(3.14); -- 4 (向上取整) SELECT floor(3.14); -- 3 (向下取整) SELECT trunc(3.14159, 2); -- 3.14 (截?cái)? -- 冪運(yùn)算 SELECT power(2, 3); -- 8 SELECT sqrt(25); -- 5 (平方根) SELECT cbrt(27); -- 3 (立方根) -- 對(duì)數(shù)函數(shù) SELECT ln(10); -- 自然對(duì)數(shù) SELECT log(100); -- 以10為底對(duì)數(shù)
（2）三角函數(shù)
SELECT pi(); -- 3.14159265358979 SELECT sin(pi()/2); -- 1 SELECT cos(0); -- 1 SELECT tan(pi()/4); -- 1 SELECT degrees(pi()); -- 180 (弧度轉(zhuǎn)角度) SELECT radians(180); -- 3.14159 (角度轉(zhuǎn)弧度)
（3）隨機(jī)數(shù)生成
SELECT random(); -- 0-1之間的隨機(jī)數(shù) SELECT setseed(0.5); -- 設(shè)置隨機(jī)種子 -- 生成指定范圍隨機(jī)整數(shù) SELECT floor(random() * 100)::int; -- 0-99隨機(jī)整數(shù) SELECT (random() * (max-min) + min)::int; -- min-max隨機(jī)整數(shù)
2. 字符串函數(shù)
（1）字符串操作
-- 長度和位置
SELECT length('Hello'); -- 5
SELECT position('l' in 'Hello'); -- 3
SELECT strpos('Hello', 'l'); -- 3
-- 大小寫轉(zhuǎn)換
SELECT upper('hello'); -- HELLO
SELECT lower('HELLO'); -- hello
SELECT initcap('hello world'); -- Hello World
-- 修剪函數(shù)
SELECT trim(' hello '); -- 'hello'
SELECT ltrim(' hello'); -- 'hello'
SELECT rtrim('hello '); -- 'hello'
SELECT trim(BOTH 'x' FROM 'xxhelloxx'); -- 'hello'
-- 填充函數(shù)
SELECT lpad('hi', 5, '*'); -- '***hi'
SELECT rpad('hi', 5, '*'); -- 'hi***'（2）子字符串操作
-- 子字符串
SELECT substring('Hello World' from 2 for 5); -- 'ello '
SELECT substr('Hello World', 2, 5); -- 'ello '
-- 字符串替換
SELECT replace('Hello World', 'World', 'PostgreSQL'); -- 'Hello PostgreSQL'
SELECT translate('12345', '123', 'abc'); -- 'abc45'
-- 字符串分割
SELECT split_part('a,b,c,d', ',', 2); -- 'b'
SELECT string_to_array('a,b,c', ','); -- {a,b,c}
SELECT array_to_string(ARRAY['a','b','c'], ','); -- 'a,b,c'（3）格式化函數(shù)
-- 字符串連接
SELECT concat('Hello', ' ', 'World'); -- 'Hello World'
SELECT 'Hello' || ' ' || 'World'; -- 'Hello World'
-- 格式化輸出
SELECT format('Hello %s, your balance is %s', 'John', 100.50);
-- 'Hello John, your balance is 100.50'
-- 重復(fù)字符串
SELECT repeat('*', 5); -- '*****'3. 日期時(shí)間函數(shù)
（1）當(dāng)前時(shí)間獲取
-- 獲取當(dāng)前時(shí)間 SELECT current_date; -- 當(dāng)前日期 SELECT current_time; -- 當(dāng)前時(shí)間 SELECT current_timestamp; -- 當(dāng)前時(shí)間戳 SELECT now(); -- 當(dāng)前時(shí)間戳（同current_timestamp） SELECT localtimestamp; -- 本地時(shí)間戳 -- 帶精度的當(dāng)前時(shí)間 SELECT current_time(2); -- 當(dāng)前時(shí)間（2位小數(shù)秒） SELECT current_timestamp(3); -- 當(dāng)前時(shí)間戳（3位小數(shù)秒）
（2）日期時(shí)間提取
-- EXTRACT函數(shù)
SELECT extract(year FROM current_timestamp); -- 年份
SELECT extract(month FROM current_timestamp); -- 月份
SELECT extract(day FROM current_timestamp); -- 日期
SELECT extract(hour FROM current_timestamp); -- 小時(shí)
SELECT extract(minute FROM current_timestamp); -- 分鐘
SELECT extract(second FROM current_timestamp); -- 秒數(shù)
SELECT extract(dow FROM current_timestamp); -- 星期幾（0-6，周日=0）
SELECT extract(doy FROM current_timestamp); -- 一年中的第幾天
-- DATE_PART函數(shù)（同EXTRACT）
SELECT date_part('year', current_timestamp);（3）日期時(shí)間運(yùn)算
-- 日期加減
SELECT current_date + INTERVAL '1 day'; -- 明天
SELECT current_date - INTERVAL '1 week'; -- 一周前
SELECT current_timestamp + INTERVAL '2 hours 30 minutes'; -- 2小時(shí)30分鐘后
-- 年齡計(jì)算
SELECT age('2000-01-01'::date); -- 從2000-01-01到現(xiàn)在的年齡
SELECT age('2000-01-01'::date, '2020-01-01'::date); -- 兩個(gè)日期之間的年齡
-- 日期截?cái)?
SELECT date_trunc('hour', current_timestamp); -- 截?cái)嗟叫r(shí)
SELECT date_trunc('day', current_timestamp); -- 截?cái)嗟教?
SELECT date_trunc('month', current_timestamp); -- 截?cái)嗟皆?/pre>（4）日期時(shí)間格式化
-- TO_CHAR格式化
SELECT to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS'); -- '2023-01-01 14:30:00'
SELECT to_char(current_timestamp, 'Day, Month DD, YYYY'); -- 'Sunday, January 01, 2023'
SELECT to_char(123.45, '999D99'); -- '123.45'
-- TO_DATE和TO_TIMESTAMP轉(zhuǎn)換
SELECT to_date('20230101', 'YYYYMMDD'); -- 2023-01-01
SELECT to_timestamp('20230101 143000', 'YYYYMMDD HH24MISS'); -- 2023-01-01 14:30:004. 條件函數(shù)
（1）條件判斷
-- CASE表達(dá)式
SELECT
name,
salary,
CASE
WHEN salary > 100000 THEN 'High'
WHEN salary > 50000 THEN 'Medium'
ELSE 'Low'
END as salary_level
FROM employees;
-- 簡單CASE
SELECT
product_type,
CASE product_type
WHEN 'A' THEN 'Type A'
WHEN 'B' THEN 'Type B'
ELSE 'Other'
END as type_description
FROM products;（2）空值處理
-- COALESCE（返回第一個(gè)非空值） SELECT coalesce(NULL, NULL, 'default'); -- 'default' SELECT coalesce(email, 'N/A') FROM users; -- 如果email為NULL則返回'N/A' -- NULLIF（相等返回NULL） SELECT nullif(5, 5); -- NULL SELECT nullif(5, 0); -- 5 -- GREATEST和LEAST SELECT greatest(1, 5, 3); -- 5 SELECT least(1, 5, 3); -- 1
5. 聚合函數(shù)
（1）基本聚合函數(shù)
-- 計(jì)數(shù) SELECT count(*) FROM table; -- 總行數(shù) SELECT count(DISTINCT column) FROM table; -- 不重復(fù)計(jì)數(shù) -- 求和與平均 SELECT sum(salary) FROM employees; SELECT avg(salary) FROM employees; -- 最大最小值 SELECT max(salary) FROM employees; SELECT min(salary) FROM employees; -- 統(tǒng)計(jì)信息 SELECT stddev(salary) FROM employees; -- 標(biāo)準(zhǔn)差 SELECT variance(salary) FROM employees; -- 方差
（2）高級(jí)聚合函數(shù)
-- 分組統(tǒng)計(jì)
SELECT
department,
count(*) as emp_count,
avg(salary) as avg_salary,
sum(salary) as total_salary
FROM employees
GROUP BY department;
-- 字符串聚合
SELECT
department,
string_agg(name, ', ') as employees
FROM employees
GROUP BY department;
-- 數(shù)組聚合
SELECT
department,
array_agg(name) as employees
FROM employees
GROUP BY department;
-- JSON聚合
SELECT
department,
json_agg(json_build_object('name', name, 'salary', salary)) as employees
FROM employees
GROUP BY department;6. 窗口函數(shù)
（1）排名函數(shù)
-- 行號(hào)、排名、密集排名
SELECT
name,
salary,
row_number() OVER (ORDER BY salary DESC) as row_num,
rank() OVER (ORDER BY salary DESC) as rank,
dense_rank() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;
-- 分區(qū)排名
SELECT
department,
name,
salary,
rank() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;（2）前后值函數(shù)
-- LAG和LEAD（訪問前后行）
SELECT
date,
sales,
lag(sales) OVER (ORDER BY date) as prev_sales,
lead(sales) OVER (ORDER BY date) as next_sales
FROM sales_data;
-- FIRST_VALUE和LAST_VALUE
SELECT
date,
sales,
first_value(sales) OVER (ORDER BY date) as first_sales,
last_value(sales) OVER (ORDER BY date) as last_sales
FROM sales_data;（3）累計(jì)和移動(dòng)平均
-- 累計(jì)計(jì)算
SELECT
date,
sales,
sum(sales) OVER (ORDER BY date) as cumulative_sum,
avg(sales) OVER (ORDER BY date) as cumulative_avg
FROM sales_data;
-- 移動(dòng)平均
SELECT
date,
sales,
avg(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg_3
FROM sales_data;7. JSON 函數(shù)（PostgreSQL 9.3+）
（1）JSON創(chuàng)建和解析
-- 創(chuàng)建JSON
SELECT json_build_object('name', 'John', 'age', 30);
SELECT json_build_array(1, 2, 3);
-- 解析JSON
SELECT json_extract_path('{"a": {"b": 1}}', 'a', 'b'); -- 1
SELECT '{"name": "John"}'::json->>'name'; -- 'John'
SELECT '{"ages": [10, 20, 30]}'::json->'ages'->>1; -- '20'（2）JSON聚合
-- 行轉(zhuǎn)JSON
SELECT row_to_json(employees) FROM employees WHERE id = 1;
-- JSON聚合
SELECT
department,
json_agg(json_build_object('name', name, 'salary', salary)) as employees
FROM employees
GROUP BY department;三、實(shí)用SQL示例
1. 數(shù)據(jù)備份和恢復(fù)
# 備份單個(gè)數(shù)據(jù)庫 pg_dump -U username -d mydatabase -f backup.sql # 備份所有數(shù)據(jù)庫 pg_dumpall -U username -f alldb_backup.sql # 壓縮備份 pg_dump -U username -d mydatabase | gzip > backup.sql.gz # 恢復(fù)數(shù)據(jù)庫 psql -U username -d mydatabase -f backup.sql # 恢復(fù)壓縮備份 gunzip -c backup.sql.gz | psql -U username -d mydatabase
2. 性能分析
-- 查看查詢計(jì)劃
EXPLAIN SELECT * FROM large_table WHERE id = 1000;
EXPLAIN ANALYZE SELECT * FROM large_table WHERE id = 1000;
-- 查看表大小和索引
SELECT
schemaname,
tablename,
tableowner,
tablesize,
indexsize
FROM pg_tables
WHERE schemaname = 'public';
-- 查看長查詢
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;3. 實(shí)用管理查詢
-- 查看鎖信息
SELECT
locktype,
relation::regclass,
mode,
granted
FROM pg_locks
WHERE relation = 'mytable'::regclass;
-- 查看連接數(shù)
SELECT
datname,
count(*) as connections
FROM pg_stat_activity
GROUP BY datname;
-- 查看表統(tǒng)計(jì)信息
SELECT
schemaname,
tablename,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch
FROM pg_stat_user_tables;四、總結(jié)
PostgreSQL 提供了豐富的命令行工具和內(nèi)置函數(shù)，使得數(shù)據(jù)庫管理和數(shù)據(jù)操作變得非常高效。關(guān)鍵點(diǎn)包括：
- psql命令行：熟練掌握連接參數(shù)和元命令，提高管理效率
- 數(shù)學(xué)函數(shù)：處理數(shù)值計(jì)算和統(tǒng)計(jì)分析
- 字符串函數(shù)：文本處理和格式化輸出
- 日期時(shí)間函數(shù)：時(shí)間計(jì)算和格式化
- 聚合函數(shù)：數(shù)據(jù)統(tǒng)計(jì)和分組分析
- 窗口函數(shù)：高級(jí)分析和排名計(jì)算
- JSON函數(shù)：處理半結(jié)構(gòu)化數(shù)據(jù)
相關(guān)文獻(xiàn)
【數(shù)據(jù)庫知識(shí)】PostgreSQL介紹
到此這篇關(guān)于PGSQL常見命令行與函數(shù)實(shí)例詳解的文章就介紹到這了,更多相關(guān)PGSQL常見命令內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了PostgreSQL 對(duì)數(shù)組的遍歷操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
利用OGG實(shí)現(xiàn)PostgreSQL實(shí)時(shí)同步的過程詳解
本文詳細(xì)闡述了利用OGG實(shí)現(xiàn)PostgreSQL實(shí)時(shí)同步的全過程,文章通過代碼示例和圖文結(jié)合講解的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作有一定的參考價(jià)值,需要的朋友可以參考下2023-11-11
PostgreSQL的外部數(shù)據(jù)封裝器fdw用法
這篇文章主要介紹了PostgreSQL的外部數(shù)據(jù)封裝器fdw用法，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL教程(三)：表的繼承和分區(qū)表詳解
這篇文章主要介紹了PostgreSQL教程(三)：表的繼承和分區(qū)表詳解,本文講解了多表繼承、 繼承和權(quán)限、什么是分區(qū)表、分區(qū)表實(shí)現(xiàn)、分區(qū)和約束排除等內(nèi)容,需要的朋友可以參考下2015-05-05
PostgreSQL中設(shè)置和管理數(shù)據(jù)庫索引的操作指南
索引是數(shù)據(jù)庫管理系統(tǒng)中的一個(gè)重要特性,能夠顯著提高查詢性能,PostgreSQL 作為一個(gè)強(qiáng)大的開源關(guān)系數(shù)據(jù)庫管理系統(tǒng),提供了多種類型的索引結(jié)構(gòu),以滿足不同的需求,本文將詳細(xì)介紹 PostgreSQL 中如何設(shè)置和管理索引,需要的朋友可以參考下2025-06-06
PostgreSQL的整型、浮點(diǎn)型、固定精度數(shù)值和序列等數(shù)字類型
PostgreSQL（簡稱PGSQL）是一種開源關(guān)系型數(shù)據(jù)庫管理系統(tǒng),廣泛應(yīng)用于企業(yè)級(jí)應(yīng)用,文章詳細(xì)介紹了PostgreSQL的數(shù)字類型,包括整型、浮點(diǎn)型、固定精度數(shù)值型和序列類型,強(qiáng)調(diào)了選擇合適的數(shù)字類型對(duì)于數(shù)據(jù)庫的存儲(chǔ)效率、查詢性能和數(shù)據(jù)準(zhǔn)確性的重要性2024-09-09
這篇文章主要介紹了postgresql重置序列起始值，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL 自定義自動(dòng)類型轉(zhuǎn)換操作(CAST)
這篇文章主要介紹了PostgreSQL 自定義自動(dòng)類型轉(zhuǎn)換操作(CAST)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL中date_trunc函數(shù)的語法及一些示例
這篇文章主要給大家介紹了關(guān)于PostgreSQL中date_trunc函數(shù)的語法及一些示例的相關(guān)資料,DATE_TRUNC函數(shù)是PostgreSQL數(shù)據(jù)庫中用于截?cái)嗳掌诓糠值暮瘮?shù),文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-04-04
postgreSQL數(shù)據(jù)庫的監(jiān)控及數(shù)據(jù)維護(hù)操作
這篇文章主要介紹了postgreSQL數(shù)據(jù)庫的監(jiān)控及數(shù)據(jù)維護(hù)操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01