Menu
CoddyTech

PostgreSQL 速查表

最后更新

psql 命令

元命令在 psql(PostgreSQL 的终端客户端)里执行,而不是在服务器上执行。它们以反斜杠开头,不需要分号。

作用命令
列出数据库\l
连接到另一个数据库\c shop
列出表\dt
列出所有模式中的表\dt *.*
查看表结构(列、索引、键)\d users
查看表结构及存储信息和注释\d+ users
列出模式\dn
列出视图\dv
列出索引\di
列出函数\df
列出角色(用户)\du
显示当前连接\conninfo
运行文件中的 SQL\i setup.sql
开启或关闭查询计时\timing
扩展显示(每列单独一行)\x
在编辑器中修改上一条查询\e
查看 SQL 语法帮助\h CREATE TABLE
列出所有元命令\?
退出 psql\q

从终端连接

作用命令
以指定用户连接到数据库psql -U postgres -d shop
连接到远程主机和端口psql -h db.example.com -p 5432 -U app -d shop
用连接 URL 连接psql postgresql://app:secret@localhost:5432/shop
运行一条查询后退出psql -d shop -c "SELECT count(*) FROM users;"
运行 SQL 文件psql -d shop -f setup.sql
在 shell 中创建数据库createdb shop
在 shell 中删除数据库dropdb shop

数据库与模式

作用SQL
创建数据库CREATE DATABASE shop;
删除数据库DROP DATABASE shop;
显示当前数据库SELECT current_database();
创建模式CREATE SCHEMA sales;
显示模式搜索顺序SHOW search_path;
优先在某个模式中查找SET search_path TO sales, public;
显示服务器版本SELECT version();

表与数据类型

作用SQL
创建带 identity 主键的表CREATE TABLE products (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) CHECK (price >= 0), created_at timestamptz DEFAULT now());
添加列ALTER TABLE users ADD COLUMN phone text;
重命名列ALTER TABLE users RENAME COLUMN country TO country_code;
修改列的类型ALTER TABLE users ALTER COLUMN age TYPE smallint;
设置默认值ALTER TABLE users ALTER COLUMN active SET DEFAULT false;
删除列ALTER TABLE users DROP COLUMN tags;
清空表并重置 identityTRUNCATE orders, users RESTART IDENTITY;
表存在时删除DROP TABLE IF EXISTS products;
用于金额的精确小数numeric(10,2)
带时区的日期和时间timestamptz
随机 UUIDSELECT gen_random_uuid();
枚举类型CREATE TYPE mood AS ENUM ('happy', 'sad');

约束与键

作用SQL
添加主键ALTER TABLE logs ADD PRIMARY KEY (id);
级联删除子行的外键CREATE TABLE reviews (id integer PRIMARY KEY, user_id integer REFERENCES users(id) ON DELETE CASCADE);
添加检查约束ALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0);
NULL 视为相等的唯一约束(15+)CREATE TABLE emails (address text UNIQUE NULLS NOT DISTINCT);
把列设为必填ALTER TABLE users ALTER COLUMN country SET NOT NULL;
删除约束ALTER TABLE users DROP CONSTRAINT users_email_key;

查询(PostgreSQL 特有写法)

标准的 SELECT、WHERE 和 JOIN 在 SQL 速查表里;这里是 PostgreSQL 特有的写法。

作用SQL
不区分大小写的匹配SELECT * FROM users WHERE name ILIKE 'a%';
正则表达式匹配SELECT * FROM users WHERE email ~ '@example\.com$';
每组取第一行SELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC;
限制行数和偏移SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
标准的行数限制SELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY;
用 :: 转换类型SELECT '42'::integer + 1;
NULL 的替代值SELECT name, COALESCE(email, 'none') FROM users;
把 NULL 视为相等的比较SELECT * FROM users WHERE email IS DISTINCT FROM 'asha@example.com';
生成数字序列SELECT * FROM generate_series(1, 5);
聚合成字符串SELECT string_agg(name, ', ' ORDER BY name) FROM users;
条件聚合SELECT count(*) FILTER (WHERE status = 'paid') AS paid, count(*) AS total FROM orders;

插入、更新、删除和 upsert

作用SQL
插入并返回新 idINSERT INTO users (name, email) VALUES ('Dana', 'dana@example.com') RETURNING id;
插入多行INSERT INTO users (name) VALUES ('Eli'), ('Femi');
Upsert(插入或更新)INSERT INTO users (name, email) VALUES ('Asha K', 'asha@example.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
插入时忽略重复INSERT INTO users (name, email) VALUES ('Ben', 'ben@example.com') ON CONFLICT DO NOTHING;
更新并查看被修改的行UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING name, salary;
根据另一张表更新UPDATE orders SET status = 'vip' FROM users WHERE orders.user_id = users.id AND users.country = 'IN';
删除并查看被删除的行DELETE FROM orders WHERE status = 'refunded' RETURNING id;
借助另一张表删除DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.active = false;
把行复制到新表CREATE TABLE paid_orders AS SELECT * FROM orders WHERE status = 'paid';

JSONB

作用SQL
以 JSON 形式取字段SELECT profile -> 'plan' FROM users;
以文本形式取字段SELECT profile ->> 'plan' FROM users;
以文本形式取嵌套字段SELECT profile #>> '{address,city}' FROM users;
JSON 包含某个值的行SELECT * FROM users WHERE profile @> '{"plan": "pro"}';
包含某个键的行SELECT * FROM users WHERE profile ? 'city';
设置键的值UPDATE users SET profile = jsonb_set(profile, '{plan}', '"team"') WHERE id = 2;
合并对象UPDATE users SET profile = profile || '{"beta": true}';
删除键UPDATE users SET profile = profile - 'city';
用列构建 JSONSELECT jsonb_build_object('id', id, 'name', name) FROM users;
把行转成 JSON 数组SELECT jsonb_agg(users) FROM users;
用于 @> 和 ? 查找的索引CREATE INDEX users_profile_idx ON users USING gin (profile);

数组

作用SQL
数组字面量SELECT ARRAY[1, 2, 3];
数组中包含某个值的行SELECT * FROM users WHERE 'sql' = ANY(tags);
数组包含全部给定值的行SELECT * FROM users WHERE tags @> ARRAY['sql', 'python'];
每个元素一行SELECT name, unnest(tags) FROM users;
追加元素UPDATE users SET tags = array_append(tags, 'sql') WHERE id = 2;
数组长度SELECT name, cardinality(tags) FROM users;

日期与时间

作用SQL
当前时间(带时区)SELECT now();
今天SELECT current_date;
加上时间间隔SELECT current_date + INTERVAL '7 days';
最近 30 天的行SELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days';
截断到月SELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1;
提取某个部分SELECT EXTRACT(YEAR FROM hired_on) FROM employees;
日期之差(天数)SELECT DATE '2026-12-25' - DATE '2026-11-17';
两个日期之间的时长SELECT name, age(current_date, hired_on) FROM employees;
把日期格式化为文本SELECT to_char(hired_on, 'DD Mon YYYY') FROM employees;
把文本解析为日期SELECT to_date('17/11/2026', 'DD/MM/YYYY');
转换到某个时区SELECT now() AT TIME ZONE 'Asia/Kolkata';

文本函数

作用SQL
拼接(遇到 NULL 结果为 NULL)SELECT name || ' <' || email || '>' FROM users;
拼接并跳过 NULLSELECT concat_ws(' ', name, email) FROM users;
大写和小写SELECT upper(name), lower(name) FROM users;
长度SELECT name, length(name) FROM users;
截取子串SELECT substring(name FROM 1 FOR 2) FROM users;
替换SELECT replace(email, '@example.com', '') FROM users;
按分隔符取其中一段SELECT split_part(email, '@', 2) FROM users;
正则表达式替换SELECT regexp_replace('a1b22c', '[0-9]+', '', 'g');
去掉首尾空格SELECT trim(' hi ');

CTE 与窗口函数

作用SQL
用 WITH 给子查询命名WITH paid AS (SELECT user_id, sum(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id) SELECT * FROM paid WHERE total > 50;
递归 CTE(数到 5)WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n;
按组给行编号SELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees;
并列排名SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees;
累计求和SELECT id, amount, sum(amount) OVER (ORDER BY id) FROM orders;
上一行的值SELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) FROM employees;
为每一行连接它自己的子查询SELECT u.name, o.amount FROM users u CROSS JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) o;

索引

作用SQL
B-tree 索引(默认)CREATE INDEX orders_user_idx ON orders (user_id);
唯一索引CREATE UNIQUE INDEX employees_name_idx ON employees (name);
多列索引CREATE INDEX orders_user_created_idx ON orders (user_id, created_at);
部分索引CREATE INDEX orders_paid_idx ON orders (created_at) WHERE status = 'paid';
表达式索引CREATE INDEX users_lower_email_idx ON users (lower(email));
覆盖索引(仅索引扫描)CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (amount);
创建时不阻塞写入CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
删除索引DROP INDEX IF EXISTS orders_user_idx;
查看执行计划EXPLAIN SELECT * FROM orders WHERE user_id = 1;
查看带实际耗时的执行计划EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

事务与锁

作用SQL
开始事务,然后保留或撤销BEGIN; UPDATE employees SET salary = 0 WHERE id = 3; ROLLBACK;
撤销事务的一部分BEGIN; INSERT INTO users (name) VALUES ('Gil'); SAVEPOINT s1; DELETE FROM users WHERE name = 'Gil'; ROLLBACK TO s1; COMMIT;
更严格的隔离级别BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM orders; COMMIT;
锁住读取的行BEGIN; SELECT * FROM orders WHERE id = 1 FOR UPDATE; COMMIT;
任务队列:跳过已锁定的行BEGIN; SELECT id FROM orders WHERE status = 'paid' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; COMMIT;

用户、角色与权限

作用SQL
创建可登录的角色CREATE ROLE app LOGIN PASSWORD 'change-me';
授予表的只读权限GRANT SELECT ON users TO app;
授予模式中所有表的读取权限GRANT SELECT ON ALL TABLES IN SCHEMA public TO app;
收回权限REVOKE SELECT ON users FROM app;
修改密码ALTER ROLE app PASSWORD 'new-secret';
显示当前用户SELECT current_user;

维护与监控

作用SQL
正在运行的查询SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle';
取消查询SELECT pg_cancel_backend(12345);
结束连接SELECT pg_terminate_backend(12345);
表及其索引的大小SELECT pg_size_pretty(pg_total_relation_size('orders'));
数据库的大小SELECT pg_size_pretty(pg_database_size(current_database()));
更新查询规划器的统计信息ANALYZE orders;
回收死行VACUUM orders;
把表重写到最小体积(会锁表)VACUUM FULL orders;
显示某个设置SHOW work_mem;
修改本会话的设置SET work_mem = '64MB';

备份与恢复

PostgreSQL 客户端工具中的 shell 命令。

作用命令
把数据库导出为 SQL 文件pg_dump -U postgres shop > shop.sql
以自定义格式导出pg_dump -Fc -U postgres shop > shop.dump
导出单张表pg_dump -t orders shop > orders.sql
恢复 SQL 导出文件psql -d shop -f shop.sql
恢复自定义格式的导出文件pg_restore -d shop shop.dump
导出所有数据库和角色pg_dumpall > cluster.sql
把查询结果导出为 CSV(在 psql 中)\copy (SELECT * FROM orders) TO 'orders.csv' CSV HEADER
导入 CSV(在 psql 中)\copy orders (user_id, amount, status) FROM 'orders.csv' CSV HEADER

常用的 psql 命令和 PostgreSQL 语句,都整理在可直接复制的表格里:先是连接和 \d 系列命令,然后是表、upsert、JSONB、日期、窗口函数、索引、角色、备份和维护。

这里的 SQL 使用 PostgreSQL 17 语法,大部分在更早的版本中也能用。其中的 SQL 语句都在 PostgreSQL 17 上验证过;想试试的话,把它粘贴到自带示例表的 PostgreSQL 在线编辑器里即可。

PostgreSQL 速查表常见问题

psql 命令是什么?
psql 是 PostgreSQL 的终端客户端。除了 SQL,它还能识别以反斜杠开头的元命令:\l 列出数据库,\c 连接到某个数据库,\dt 列出表,\d users 查看表结构,\q 退出。这些命令由 psql 自己执行,所以不需要分号,在其他客户端中也不能用。
如何列出 PostgreSQL 中的所有表?
在 psql 中输入 \dt(所有模式用 \dt *.*)。用 SQL 的话,在任何客户端中运行:SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
PostgreSQL 的 SQL 和 MySQL 不一样吗?
核心部分(SELECT、JOIN、GROUP BY)是一样的。PostgreSQL 还增加了 ILIKE、:: 类型转换、RETURNING、DISTINCT ON、ON CONFLICT upsert、数组和 JSONB,并用 identity 列代替 MySQL 的 AUTO_INCREMENT。
JSON 和 JSONB 有什么区别?
json 按原样保存文本;jsonb 保存解析后的二进制形式,会去掉重复的键和空白,并支持索引(GIN)以及 @> 和 ? 等运算符。除非需要保留原始文本,否则用 jsonb。
可以在线练习这些查询吗?
可以。PostgreSQL 在线编辑器在你的浏览器里用真实的 PostgreSQL 17 数据库运行任何查询,并提供可以编辑的示例表。需要服务器权限的语句,比如 CREATE DATABASE 或 CREATE ROLE,需要在你自己的 PostgreSQL 上运行。
这份 PostgreSQL 速查表免费吗?

免费。可以免费阅读、复制和打印,无需注册。

Coddy 编程语言插图

在 Coddy 学习 PostgreSQL

开始