PostgreSQL 速查表

本章提供 PostgreSQL 常用命令和操作的快速参考。

连接命令

# 本地连接
psql -U postgres

# 指定数据库连接
psql -U postgres -d mydb

# 指定主机和端口
psql -h localhost -p 5432 -U postgres -d mydb

# 执行命令后退出
psql -U postgres -d mydb -c "SELECT version();"

# 导入 SQL 文件
psql -U postgres -d mydb < backup.sql

# 导出查询结果
psql -U postgres -d mydb -c "SELECT * FROM users" -o output.txt

psql 常用命令

# 数据库操作
\l                  # 列出所有数据库
\l+ mydb           # 查看数据库详情

# 表操作
\d                  # 列出当前数据库所有表
\d users            # 查看 users 表结构
\d+ users           # 详细查看 users 表
\dt                 # 列出所有表

# 索引操作
\di                 # 列出所有索引

# 用户和权限
\du                 # 列出所有用户
\du+                # 详细列出用户

# 其他
\?                  # 帮助
\h                  # SQL 命令帮助
\h SELECT           # 查看 SELECT 命令帮助
\! cmd              # 执行 shell 命令
\c mydb            # 切换数据库
\conninfo          # 查看连接信息
\x                  # 切换扩展显示模式
\timing            # 开启/关闭执行时间显示

数据库操作

-- 创建数据库
CREATE DATABASE mydb;

-- 创建数据库（带选项）
CREATE DATABASE mydb 
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    TEMPLATE = template0;

-- 删除数据库
DROP DATABASE IF EXISTS mydb;

-- 查看数据库
SELECT datname FROM pg_database;

-- 查看当前数据库
SELECT current_database();

表操作

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100),
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看表结构
\d users
SELECT * FROM information_schema.columns 
WHERE table_name = 'users';

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 修改列
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

-- 重命名表
ALTER TABLE users RENAME TO app_users;

-- 删除表
DROP TABLE IF EXISTS users;

数据类型

数值类型

-- 整数
SMALLINT, INTEGER, BIGINT

-- 浮点数
REAL, DOUBLE PRECISION, NUMERIC(10, 2)

-- 自增
SERIAL, BIGSERIAL

字符串类型

CHAR(10)     -- 固定长度
VARCHAR(100) -- 可变长度
TEXT         -- 无限制

日期时间类型

DATE         -- 日期
TIME         -- 时间
TIMESTAMP    -- 日期时间（无时区）
TIMESTAMPTZ  -- 日期时间（带时区）
INTERVAL     -- 时间间隔

JSON 类型

JSON   -- 原始 JSON
JSONB  -- 二进制 JSON（推荐）

数据操作

INSERT 插入

-- 插入单行
INSERT INTO users (username, email) VALUES ('john', '[email protected]');

-- 插入多行
INSERT INTO users (username, email) VALUES 
    ('a', '[email protected]'),
    ('b', '[email protected]');

-- 插入并返回
INSERT INTO users (username) VALUES ('john') RETURNING id;

SELECT 查询

-- 基本查询
SELECT * FROM users;
SELECT username, email FROM users;

-- 条件查询
SELECT * FROM users WHERE age >= 18;

-- 排序
SELECT * FROM users ORDER BY created_at DESC;

-- 分页
SELECT * FROM users LIMIT 10 OFFSET 20;

-- 去重
SELECT DISTINCT department FROM users;

UPDATE 更新

-- 更新数据
UPDATE users SET age = 25 WHERE username = 'john';

-- 批量更新
UPDATE users SET age = age + 1 WHERE status = 'active';

-- 更新并返回
UPDATE users SET age = 30 WHERE id = 1 RETURNING *;

DELETE 删除

-- 删除数据
DELETE FROM users WHERE id = 1;

-- 清空表
DELETE FROM users;
-- 或
TRUNCATE TABLE users RESTART IDENTITY;

条件查询

-- WHERE 条件
WHERE age = 25
WHERE age != 25
WHERE age > 25
WHERE age >= 25 AND age <= 30
WHERE age BETWEEN 20 AND 30
WHERE name IN ('john', 'jane')
WHERE name LIKE 'j%'
WHERE name ILIKE 'JOHN'  -- 不区分大小写
WHERE name IS NULL
WHERE name IS NOT NULL

连接查询

-- 内连接
SELECT * FROM a INNER JOIN b ON a.id = b.a_id;

-- 左连接
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;

-- 右连接
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;

-- 全外连接
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id;

-- 自连接
SELECT a.name, b.name AS manager
FROM employees a
LEFT JOIN employees b ON a.manager_id = b.id;

聚合函数

-- 常用聚合
COUNT(*)      -- 行数
SUM(column)   -- 求和
AVG(column)   -- 平均
MAX(column)   -- 最大
MIN(column)   -- 最小

-- 分组聚合
SELECT department, COUNT(*), AVG(salary)
FROM users
GROUP BY department
HAVING AVG(salary) > 5000;

-- 字符串聚合
STRING_AGG(name, ',')           -- 逗号分隔
STRING_AGG(name, ',' ORDER BY salary)  -- 带排序

窗口函数

-- 排名
ROW_NUMBER() OVER (ORDER BY salary DESC)
RANK() OVER (ORDER BY salary DESC)
DENSE_RANK() OVER (ORDER BY salary DESC)

-- 前后值
LAG(salary) OVER (ORDER BY id)
LEAD(salary) OVER (ORDER BY id)

-- 聚合窗口
SUM(salary) OVER (PARTITION BY department)
AVG(salary) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

视图操作

-- 创建视图
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

-- 创建或替换视图
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email FROM users WHERE status = 'active';

-- 查看视图定义
SELECT definition FROM pg_views WHERE viewname = 'active_users';

-- 删除视图
DROP VIEW IF EXISTS active_users;

物化视图

-- 创建物化视图
CREATE MATERIALIZED VIEW order_summary AS
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

-- 创建带唯一索引的物化视图（支持并发刷新）
CREATE MATERIALIZED VIEW daily_stats AS
SELECT date, COUNT(*) AS count FROM events GROUP BY date;
CREATE UNIQUE INDEX idx_daily_stats_date ON daily_stats(date);

-- 刷新物化视图
REFRESH MATERIALIZED VIEW order_summary;

-- 并发刷新（不阻塞查询，需要唯一索引）
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_stats;

-- 查看物化视图
SELECT * FROM pg_matviews;

-- 删除物化视图
DROP MATERIALIZED VIEW IF EXISTS order_summary;

索引操作

-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_dept_salary ON users(department, salary);
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 创建特殊索引
CREATE INDEX idx_users_email_gin ON users USING GIN (email);
CREATE INDEX idx_users_name_lower ON users (LOWER(name));

-- 查看索引
SELECT * FROM pg_indexes WHERE tablename = 'users';

-- 删除索引
DROP INDEX idx_users_email;

事务控制

-- 开始事务
BEGIN;
-- 或
START TRANSACTION;

-- 提交
COMMIT;

-- 回滚
ROLLBACK;

-- 保存点
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;

用户和权限

-- 创建用户
CREATE USER john WITH PASSWORD 'password';

-- 授予权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO john;
GRANT SELECT, INSERT ON users TO john;
GRANT ALL ON ALL TABLES IN SCHEMA public TO john;

-- 撤销权限
REVOKE INSERT ON users FROM john;

-- 查看权限
\dp users

-- 删除用户
DROP USER john;

备份和恢复

# 备份数据库
pg_dump -U postgres mydb > backup.sql

# 备份表
pg_dump -U postgres -t users mydb > users.sql

# 恢复数据库
psql -U postgres mydb < backup.sql

# 备份所有数据库
pg_dumpall -U postgres > all_databases.sql

# 只备份结构
pg_dump -U postgres -s mydb > schema.sql

# 只备份数据
pg_dump -U postgres -a mydb > data.sql

常用函数

字符串函数

LENGTH('hello')           -- 5
UPPER('hello')           -- 'HELLO'
LOWER('HELLO')           -- 'hello'
TRIM('  hello  ')        -- 'hello'
SUBSTRING('hello', 1, 3) -- 'hel'
REPLACE('hello', 'l', 'x') -- 'hexxo'
CONCAT('a', 'b', 'c')    -- 'abc'
CONCAT_WS('-', 'a', 'b') -- 'a-b'
LEFT('hello', 3)         -- 'hel'
RIGHT('hello', 3)        -- 'llo'
LPAD('hi', 5, '0')       -- '000hi'
RPAD('hi', 5, '0')       -- 'hi000'
SPLIT_PART('a,b,c', ',', 2) -- 'b'
POSITION('ll' IN 'hello') -- 3
REVERSE('hello')          -- 'olleh'
REGEXP_REPLACE('abc123', '[0-9]', 'X', 'g') -- 'abcXXX'
REGEXP_MATCHES('abc123', '[0-9]+') -- {123}

数值函数

ROUND(3.567, 2)          -- 3.57
CEIL(3.1)                -- 4
FLOOR(3.9)               -- 3
ABS(-5)                  -- 5
MOD(10, 3)               -- 1
POWER(2, 3)              -- 8
SQRT(16)                 -- 4
SIGN(-5)                 -- -1
TRUNC(3.567, 2)          -- 3.56
RANDOM()                 -- 0-1 之间的随机数
WIDTH_BUCKET(5.5, 0, 10, 5) -- 3 (分桶函数)

日期函数

NOW()                    -- 当前时间戳
CURRENT_DATE             -- 当前日期
CURRENT_TIME             -- 当前时间
CURRENT_TIMESTAMP        -- 当前时间戳（带时区）
EXTRACT(YEAR FROM now()) -- 年份
EXTRACT(MONTH FROM now()) -- 月份
EXTRACT(DAY FROM now())  -- 日期
EXTRACT(DOW FROM now())  -- 星期几（0-6，周日为0）
EXTRACT(WEEK FROM now()) -- 年中第几周
DATE_TRUNC('day', now()) -- 日期截断到天
DATE_TRUNC('month', now()) -- 日期截断到月
AGE('2024-12-31', '2024-01-01') -- 时间间隔
DATE '2024-01-01' + INTERVAL '7 days' -- 日期运算
TO_CHAR(now(), 'YYYY-MM-DD HH24:MI:SS') -- 格式化
TO_DATE('20240115', 'YYYYMMDD') -- 字符串转日期
MAKE_DATE(2024, 1, 15)   -- 构造日期
MAKE_INTERVAL(days => 7) -- 构造间隔

条件函数

-- CASE
CASE 
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    ELSE 'C'
END

-- COALESCE：返回第一个非 NULL 值
COALESCE(NULL, NULL, 'default')  -- 'default'
COALESCE(price, 0)       -- 如果 price 为 NULL，返回 0

-- NULLIF：如果两值相等返回 NULL
NULLIF(10, 10)           -- NULL
NULLIF(10, 20)           -- 10

-- GREATEST：返回最大值
GREATEST(1, 5, 3, 2)     -- 5

-- LEAST：返回最小值
LEAST(1, 5, 3, 2)        -- 1

聚合函数

COUNT(*)                 -- 总行数
COUNT(column)            -- 非 NULL 行数
COUNT(DISTINCT column)   -- 去重计数
SUM(column)              -- 求和
AVG(column)              -- 平均值
MAX(column)              -- 最大值
MIN(column)              -- 最小值
STDDEV(column)           -- 标准差
VARIANCE(column)         -- 方差
STRING_AGG(name, ',')    -- 字符串聚合
ARRAY_AGG(name)          -- 数组聚合
JSON_AGG(name)           -- JSON 数组聚合
BOOL_AND(condition)      -- 所有行都为 true 则返回 true
BOOL_OR(condition)       -- 任一行为 true 则返回 true

窗口函数

ROW_NUMBER() OVER (ORDER BY col)    -- 行号
RANK() OVER (ORDER BY col)          -- 排名（可跳跃）
DENSE_RANK() OVER (ORDER BY col)    -- 排名（不跳跃）
LAG(col, 1) OVER (ORDER BY col)     -- 前一行
LEAD(col, 1) OVER (ORDER BY col)    -- 后一行
FIRST_VALUE(col) OVER (...)         -- 分区第一个值
LAST_VALUE(col) OVER (...)          -- 分区最后一个值
NTILE(4) OVER (ORDER BY col)        -- 分成 4 组
SUM(col) OVER (PARTITION BY grp)    -- 分组累计
SUM(col) OVER (ORDER BY col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- 累计和

公用表表达式（CTE）

-- 基本 CTE
WITH cte_name AS (
    SELECT * FROM table_name WHERE condition
)
SELECT * FROM cte_name;

-- 多个 CTE
WITH 
cte1 AS (SELECT ...),
cte2 AS (SELECT ... FROM cte1)
SELECT * FROM cte2;

-- 递归 CTE
WITH RECURSIVE hierarchy AS (
    SELECT id, name, parent_id, 1 AS level
    FROM employees WHERE parent_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.parent_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.parent_id = h.id
)
SELECT * FROM hierarchy;

-- 生成数字序列
WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;

-- 数据修改 CTE
WITH deleted AS (
    DELETE FROM old_data WHERE date < '2023-01-01'
    RETURNING *
)
INSERT INTO archive SELECT * FROM deleted;

-- 物化控制
WITH cte AS MATERIALIZED (SELECT ...)     -- 强制物化
WITH cte AS NOT MATERIALIZED (SELECT ...) -- 不物化，内联优化

JSON 操作

-- 创建 JSON
'{"name": "John", "age": 30}'::json

-- 提取字段
data->>'name'    -- 获取为文本
data->'name'     -- 获取为 JSON

-- 查询
WHERE data @> '{"age": 30}'
WHERE data ? 'name'

-- 函数
JSON_OBJECT_AGG(key, value)
JSON_AGG(column)

性能分析

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 详细分析（实际执行）
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

-- 查看慢查询日志
SHOW log_min_duration_statement;
SELECT * FROM pg_stat_statements;

-- 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('users'));
SELECT pg_size_pretty(pg_relation_size('users'));

系统信息

-- 版本信息
SELECT version();

-- 当前用户
SELECT current_user;
SELECT user;

-- 数据库信息
SELECT current_database();
SELECT current_schema();

-- 配置信息
SHOW max_connections;
SELECT * FROM pg_settings;

PostgreSQL 18 新特性速查

Skip Scan 索引

-- Skip Scan 允许在没有对前列设置条件时使用多列索引
CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);

-- PostgreSQL 18+ 可以使用索引，即使没有 customer_id 条件
SELECT * FROM orders WHERE order_date > '2024-01-01';

虚拟生成列

-- 虚拟生成列（读取时计算，默认）
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price NUMERIC,
    quantity INTEGER,
    total NUMERIC GENERATED ALWAYS AS (price * quantity) VIRTUAL
);

-- 存储生成列（写入时计算并存储）
CREATE TABLE products_stored (
    id SERIAL PRIMARY KEY,
    price NUMERIC,
    quantity INTEGER,
    total NUMERIC GENERATED ALWAYS AS (price * quantity) STORED
);

RETURNING OLD/NEW

-- UPDATE 返回新旧值
UPDATE products SET price = price * 1.1
RETURNING OLD.price AS old_price, NEW.price AS new_price;

-- DELETE 返回被删除的值
DELETE FROM products WHERE id = 1
RETURNING OLD.name, OLD.price;

UUIDv7

-- 时间可排序的 UUID（PostgreSQL 18+）
SELECT uuidv7();

-- 传统随机 UUID
SELECT uuidv4();

新增数组函数

-- 数组排序（PostgreSQL 18+）
SELECT array_sort(ARRAY[3, 1, 4, 1, 5]);
-- 结果：{1,1,3,4,5}

-- 数组反转（PostgreSQL 18+）
SELECT array_reverse(ARRAY[1, 2, 3, 4, 5]);
-- 结果：{5,4,3,2,1}

PostgreSQL 17 新特性速查

JSON_TABLE

-- 将 JSON 转换为表
SELECT *
FROM orders o,
JSON_TABLE(
    o.data,
    '$.items[*]'
    COLUMNS (
        product TEXT PATH '$.product',
        quantity INTEGER PATH '$.quantity',
        price NUMERIC PATH '$.price'
    )
) AS jt;

SQL/JSON 函数

-- JSON 构造函数
SELECT JSON_OBJECT('name': 'John', 'age': 30);
SELECT JSON_ARRAY(1, 2, 3, 'four');

-- JSON 路径查询增强
SELECT * FROM orders 
WHERE data @? '$.items[*] ? (@.price > 1000)';

复制与高可用

流复制管理

-- 主库：查看复制状态
SELECT pid, usename, application_name, client_addr, state, 
       sent_lsn, replay_lsn, sync_state
FROM pg_stat_replication;

-- 从库：查看复制状态
SELECT pg_is_in_recovery();  -- true 表示从库
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();

-- 计算复制延迟
SELECT now() - pg_last_xact_replay_timestamp() AS delay;

-- 创建复制槽
SELECT pg_create_physical_replication_slot('standby_slot');

-- 删除复制槽
SELECT pg_drop_replication_slot('standby_slot');

-- 查看复制槽
SELECT * FROM pg_replication_slots;

-- 从库提升为主库
SELECT pg_promote();

逻辑复制

-- 创建发布
CREATE PUBLICATION my_pub FOR ALL TABLES;
CREATE PUBLICATION my_pub FOR TABLE users, orders;

-- 创建订阅
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=primary port=5432 dbname=mydb user=repmgr'
    PUBLICATION my_pub;

-- 管理订阅
ALTER SUBSCRIPTION my_sub ENABLE;
ALTER SUBSCRIPTION my_sub DISABLE;
ALTER SUBSCRIPTION my_sub REFRESH PUBLICATION;

-- 查看订阅状态
SELECT * FROM pg_stat_subscription;

复制配置参数

# postgresql.conf - 主库
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
max_replication_slots = 10
synchronous_standby_names = 'FIRST 1 (standby1)'  # 同步复制

# postgresql.conf - 从库
primary_conninfo = 'host=primary port=5432 user=replicator'
hot_standby = on

小结

本速查表涵盖了 PostgreSQL 的常用操作：

连接和 psql 命令
数据库和表操作
数据类型
CRUD 操作
查询和连接
聚合和窗口函数
索引操作
事务控制
用户和权限
备份恢复
常用函数
JSON 操作
性能分析
复制与高可用
PostgreSQL 17/18 新特性

连接命令​

psql 常用命令​

数据库操作​

表操作​

数据类型​

数值类型​

字符串类型​

日期时间类型​

JSON 类型​

数据操作​

INSERT 插入​

SELECT 查询​

UPDATE 更新​

DELETE 删除​

条件查询​

连接查询​

聚合函数​

窗口函数​

视图操作​

物化视图​

索引操作​

事务控制​

用户和权限​

备份和恢复​

常用函数​

字符串函数​

数值函数​

日期函数​

条件函数​

聚合函数​

窗口函数​

公用表表达式（CTE）​

JSON 操作​

性能分析​

系统信息​

PostgreSQL 18 新特性速查​

Skip Scan 索引​

虚拟生成列​

RETURNING OLD/NEW​

UUIDv7​

新增数组函数​

PostgreSQL 17 新特性速查​

JSON_TABLE​

SQL/JSON 函数​

复制与高可用​

流复制管理​

逻辑复制​

复制配置参数​

小结​

连接命令

psql 常用命令

数据库操作

表操作

数据类型

数值类型

字符串类型

日期时间类型

JSON 类型

数据操作

INSERT 插入

SELECT 查询

UPDATE 更新

DELETE 删除

条件查询

连接查询

聚合函数

窗口函数

视图操作

物化视图

索引操作

事务控制

用户和权限

备份和恢复

常用函数

字符串函数

数值函数

日期函数

条件函数

聚合函数

窗口函数

公用表表达式（CTE）

JSON 操作

性能分析

系统信息

PostgreSQL 18 新特性速查

Skip Scan 索引

虚拟生成列

RETURNING OLD/NEW

UUIDv7

新增数组函数

PostgreSQL 17 新特性速查

JSON_TABLE

SQL/JSON 函数

复制与高可用

流复制管理

逻辑复制

复制配置参数

小结