PostgreSQL 命令速查

PostgreSQL 快速参考：psql 客户端命令、DDL/DML、连接与聚合、索引、事务与备份恢复，并附带常用参数和实操示例。

35 条命令

连接

psql -h <host> -p <port> -U <user> -d <db>

对本地或远程数据库打开一个交互式 psql 会话。

-h 主机；-p 5432 默认；-U 用户；-d 数据库；-W 强制密码；-f 文件执行脚本；-c 单条命令

PGPASSWORD=secret psql -h db.example.com -U app -d app_production

psql -U <user> <db> --variable='ON_ERROR_STOP=1' -f <file.sql>

对数据库执行 SQL 脚本，遇错即停。

psql -U app -d app -v ON_ERROR_STOP=1 -f migrations/0001_init.sql

psql 元命令

\l[+] \c[onnect] [<db>]

列出数据库；然后连接到其中之一。

\l+ 显示大小/表空间信息；\c db user 切换目标

\l+; \c app_dev

\dt[+] [<pattern>]

列出当前 schema（默认 public）中匹配模式的表。

\dt+ 显示大小与说明；\dt *.* 跨所有 schema；\d 表查看表结构

\dt+ public.*

\d <table|view|index|seq|matview>

查看对象的结构：列、索引、外键、注释。

\d orders

\dn / \du / \dv / \di

分别列出 schema / 角色 / 视图 / 索引。

\du; \di+ idx_orders_user_id

\timing / \x / \? / \q

开关查询计时、扩展显示模式、查看帮助、退出 psql。

\timing on; \x auto; SELECT * FROM orders LIMIT 2; \q

\copy <table> FROM '<file>' DELIMITER ',' CSV HEADER

从文件批量加载数据（服务端执行，但读取的是客户端文件系统）。

\copy orders(order_id,user_id,total,created_at) FROM '/tmp/orders.csv' WITH CSV HEADER

数据库与 Schema

CREATE DATABASE <db>

创建一个新数据库，可指定模板数据库相关选项。

OWNER 角色；TEMPLATE template0；ENCODING 'UTF8'；LC_COLLATE

CREATE DATABASE app_dev OWNER app_user ENCODING 'UTF8' TEMPLATE template0

DROP DATABASE <db>

删除一个数据库（不得处于被使用状态；常配合 IF EXISTS + FORCE）。

DROP DATABASE WITH (FORCE)；postgres 13+

DROP DATABASE IF EXISTS app_old;

CREATE SCHEMA / DROP SCHEMA

用于组织表/视图的命名空间，是默认 search_path 的一部分。

CREATE SCHEMA IF NOT EXISTS audit; DROP SCHEMA staging CASCADE;

表与 DDL

CREATE TABLE

创建一个新表，定义列、类型、默认值与约束。

PRIMARY KEY；FOREIGN KEY ... REFERENCES；UNIQUE；CHECK；INHERITS；PARTITION BY RANGE|LIST

CREATE TABLE orders (id bigserial PRIMARY KEY, user_id int NOT NULL REFERENCES users(id), total numeric(10,2) NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now());

ALTER TABLE

增/删/重命名列、修改类型、添加约束、设置存储参数。

ADD COLUMN；DROP COLUMN；RENAME TO；ADD CONSTRAINT；ALTER COLUMN ... TYPE

ALTER TABLE orders ADD COLUMN currency char(3) NOT NULL DEFAULT 'USD', ADD CONSTRAINT chk_cur CHECK (currency IN ('USD','EUR','CNY'));

DROP TABLE / TRUNCATE

删除表（被外键引用时需 CASCADE）；TRUNCATE 用于快速清空。

DROP TABLE ... CASCADE；TRUNCATE ... RESTART IDENTITY CASCADE

TRUNCATE TABLE staging.events RESTART IDENTITY CASCADE;

COMMENT ON <table|column> IS '...'

为数据库对象添加注释——在 \d+ 与 IDE 提示中可见。

COMMENT ON COLUMN orders.total IS '税前小计'

DML

INSERT INTO <table> [(cols)] VALUES (...) [RETURNING ...]

插入一行或多行；RETURNING 可返回被插入行的字段。

INSERT ... SELECT；ON CONFLICT DO NOTHING / DO UPDATE（upsert）；RETURNING

INSERT INTO orders(user_id, total) VALUES (42, 19.95) RETURNING id, created_at;

UPDATE ... SET ... [WHERE ...] [RETURNING ...]

修改匹配的行；除非有意为之，请始终加 WHERE。

UPDATE ... FROM t2 WHERE ...；CTE (WITH ...) + UPDATE；... RETURNING

UPDATE orders SET status='paid' WHERE user_id = 42 AND created_at < now() - interval '7 days' RETURNING id;

DELETE FROM ... [WHERE ...] [RETURNING ...]

删除匹配的行；TRUNCATE 在清空整张表时更快。

DELETE ... USING t2 WHERE ...；RETURNING

DELETE FROM orders WHERE status='cancelled' AND created_at < now() - interval '30 days' RETURNING id;

查询与过滤

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT N OFFSET M

读取行，并按条件筛选、排序、分页。

WHERE col op ANY/ALL(array)；LIMIT n OFFSET n；FETCH FIRST n ROWS ONLY；FOR UPDATE/SHARE 加锁

SELECT id, status, total FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10 OFFSET 20;

DISTINCT / GROUP BY / HAVING

去重、按列聚合、再对聚合结果过滤。

GROUP BY ROLLUP(a,b)；GROUPING SETS；HAVING 对聚合过滤；FILTER (WHERE ...) 保留 NULL

SELECT user_id, count(*) FILTER (WHERE status='paid') AS paid, sum(total) AS gmv FROM orders GROUP BY user_id HAVING count(*) >= 3;

连接

[INNER | LEFT | RIGHT | FULL] JOIN ... ON ...

组合多张表的行；LEFT JOIN 保留左侧未匹配的行。

JOIN LATERAL；NATURAL JOIN；USING(col) 替代 ON；CROSS JOIN 笛卡尔积

SELECT u.email, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.email;

WITH cte AS (...) SELECT ...

把子查询物化为 CTE；递归 CTE 可处理层级/图结构。

WITH RECURSIVE tree AS (SELECT id, parent_id, name FROM cats WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name FROM cats c JOIN tree t ON c.parent_id = t.id) SELECT * FROM tree;

聚合

count() / sum() / avg() / min() / max() / array_agg() / string_agg()

内置聚合函数；与 FILTER 子句或 GROUP BY 配合非常好用。

count/sum 内可用 DISTINCT；array_agg(string_agg) 内可用 ORDER BY

SELECT date_trunc('day', created_at) AS day, sum(total) AS gmv FROM orders GROUP BY day ORDER BY day DESC LIMIT 7;

索引与视图

CREATE INDEX ...

加速 WHERE / ORDER BY；部分索引可针对热点子集。

CONCURRENTLY（不加排他锁）；INCLUDE (a,b) 覆盖索引；USING btree|gin|gist|hash；partial WHERE

CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders(user_id, created_at DESC) INCLUDE (total);

EXPLAIN [ANALYZE] <query>

查看查询计划；ANALYZE 会实际执行并报告真实耗时与行数。

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)；FORMAT JSON / TEXT / YAML

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

VACUUM / ANALYZE / REINDEX

维护存储与统计信息：回收死元组、刷新规划器统计、重建索引。

VACUUM (FULL) 重写整表；ANALYZE 更新统计；REINDEX 重建索引；autovacuum 通常会自动处理

VACUUM (ANALYZE) orders;

CREATE VIEW / MATERIALIZED VIEW

存储一段查询；物化视图会真正缓存结果（需要刷新）。

CREATE OR REPLACE VIEW；MATERIALIZED VIEW ... WITH NO DATA；REFRESH MATERIALIZED VIEW CONCURRENTLY

CREATE MATERIALIZED VIEW mv_user_orders AS SELECT user_id, count(*) FROM orders GROUP BY user_id; CREATE UNIQUE INDEX ON mv_user_orders(user_id);

事务

BEGIN / COMMIT / ROLLBACK

把多条语句组成一个原子的工作单元（ACID）。

BEGIN ISOLATION LEVEL SERIALIZABLE；COMMIT/ROLLBACK AND CHAIN；SAVEPOINT 标签

BEGIN ISOLATION LEVEL REPEATABLE READ; UPDATE orders SET ... WHERE ...; 出错 ROLLBACK; 成功 COMMIT;

SELECT ... FOR UPDATE / FOR SHARE

在事务结束前锁住选中的行，防止并发更新。

FOR UPDATE / SHARE；NOWAIT 或 SKIP LOCKED 适合队列

SELECT id FROM jobs WHERE status='queued' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

备份与恢复

pg_dump <db> -Fc -f <file.dump>

把数据库转储为自定义格式文件（支持并行恢复、按需恢复）。

-Fc custom；-Ft tar；-Fp 纯 SQL；--schema=；--exclude-table=；-j 并行（仅 -Fd）

pg_dump -Fc -d app -f /backup/app-$(date +%F).dump

pg_restore -d <db> <file.dump>

恢复自定义/tar/dir 格式的转储；只恢复需要的 schema/data/roles。

--clean 先删除对象；--if-exists；--schema=；--table=；--jobs=N；--no-owner；--single-transaction

pg_restore -d app_dev --clean --if-exists --jobs=4 /backup/app.dump

psql -d <db> -f <file.sql>

恢复纯 SQL 转储（pg_dump -Fp 输出）。

psql -U app -d app_new -v ON_ERROR_STOP=1 -f /backup/app.sql

用户与权限

CREATE ROLE / ALTER ROLE

角色可以登录（LOGIN）、拥有对象；可单独为角色授予属性。

CREATE ROLE reporting LOGIN PASSWORD '...' NOSUPERUSER NOCREATEDB; ALTER ROLE app SET statement_timeout = '5s'

GRANT / REVOKE

对表、schema、数据库、函数授权；支持列级权限。

GRANT SELECT, INSERT ON TABLE ...；GRANT USAGE ON SCHEMA ...；WITH GRANT OPTION

GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;

\dt <schema>.* / pg_hba.conf

调整 search_path 与可见对象（\dt public.*）；pg_hba.conf 控制谁能连接。

SHOW search_path；ALTER ROLE app IN DATABASE app SET search_path = app, public；pg_hba.conf hostssl all all 0.0.0.0/0 scram-sha-256

ALTER ROLE app SET search_path = app, public;

速查页版本 1.0.0

适用于 PostgreSQL 14+