PostgreSQL 快速参考：开发人员速查表

PostgreSQL 快速参考

目录

日常快速参考
PostgreSQL
工作：连接、SQL 语法、psql 元命令、性能、JSON、窗口函数等。

一份简洁的 SQL 快速参考，涵盖最重要的命令和操作，也已提供。

postgresql logo

连接与基础

# 连接
psql -h HOST -p 5432 -U USER -d DB
psql $DATABASE_URL

# 在 psql 中
\conninfo             -- 显示连接信息
\l[+]                 -- 列出数据库
\c DB                 -- 连接到数据库
\dt[+] [schema.]pat   -- 列出表
\dv[+]                -- 列出视图
\ds[+]                -- 列出序列
\df[+] [pat]          -- 列出函数
\d[S+] name           -- 描述表/视图/序列
\dn[+]                -- 列出模式
\du[+]                -- 列出角色
\timing               -- 切换查询计时
\x                    -- 扩展显示
\e                    -- 在 $EDITOR 中编辑缓冲区
\i file.sql           -- 运行文件
\copy ...             -- 客户端 COPY
\! shell_cmd          -- 运行 shell 命令

数据类型（常用）

  • 数值类型：smallint，integer，bigint，decimal(p,s)，numeric，real，double precision，serial，bigserial
  • 文本类型：text，varchar(n)，char(n)
  • 布尔类型：boolean
  • 时间类型：timestamp [with/without time zone]，date，time，interval
  • UUID 类型：uuid
  • JSON 类型：json，jsonb（推荐）
  • 数组类型：type[]，例如 text[]
  • 网络类型：inet，cidr，macaddr
  • 几何类型：point，line，polygon 等

检查 PostgreSQL 版本

SELECT version();

PostgreSQL 服务器版本：

pg_config --version

PostgreSQL 客户端版本：

psql --version

DDL（创建 / 修改）

-- 创建模式和表
CREATE SCHEMA IF NOT EXISTS app;
CREATE TABLE app.users (
  id           bigserial PRIMARY KEY,
  email        text NOT NULL UNIQUE,
  name         text,
  active       boolean NOT NULL DEFAULT true,
  created_at   timestamptz NOT NULL DEFAULT now(),
  profile      jsonb,
  tags         text[]
);

-- 修改
ALTER TABLE app.users ADD COLUMN last_login timestamptz;
ALTER TABLE app.users ALTER COLUMN name SET NOT NULL;
ALTER TABLE app.users DROP COLUMN tags;

-- 约束
ALTER TABLE app.users ADD CONSTRAINT email_lower_uk UNIQUE (lower(email));

-- 索引
CREATE INDEX ON app.users (email);
CREATE UNIQUE INDEX CONCURRENTLY users_email_uidx ON app.users (lower(email));
CREATE INDEX users_profile_gin ON app.users USING gin (profile);
CREATE INDEX users_created_at_idx ON app.users (created_at DESC);

DML（插入 / 更新 / 合并 / 删除）

INSERT INTO app.users (email, name) VALUES
  ('a@x.com','A'), ('b@x.com','B')
RETURNING id;

-- 合并（ON CONFLICT）
INSERT INTO app.users (email, name)
VALUES ('a@x.com','Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = now();

UPDATE app.users SET active = false WHERE last_login < now() - interval '1 year';
DELETE FROM app.users WHERE active = false AND last_login IS NULL;

查询基础

SELECT * FROM app.users ORDER BY created_at DESC LIMIT 20 OFFSET 40; -- 分页

-- 过滤
SELECT * FROM app.users WHERE email ILIKE '%@example.%' AND active;

-- 聚合与 GROUP BY
SELECT active, count(*) AS n
FROM app.users
GROUP BY active
HAVING count(*) > 10;

-- 连接
SELECT o.id, u.email, o.total
FROM app.orders o
JOIN app.users u ON u.id = o.user_id
LEFT JOIN app.discounts d ON d.id = o.discount_id;

-- DISTINCT ON（PostgreSQL 特有）
SELECT DISTINCT ON (user_id) user_id, status, created_at
FROM app.events
ORDER BY user_id, created_at DESC;

-- CTE
WITH recent AS (
  SELECT * FROM app.orders WHERE created_at > now() - interval '30 days'
)
SELECT count(*) FROM recent;

-- 递归 CTE
WITH RECURSIVE t(n) AS (
  SELECT 1
  UNION ALL
  SELECT n+1 FROM t WHERE n < 10
)
SELECT sum(n) FROM t;

窗口函数

SELECT
  user_id,
  created_at,
  sum(total)      OVER (PARTITION BY user_id ORDER BY created_at) AS running_total,
  row_number()    OVER (PARTITION BY user_id ORDER BY created_at) AS rn,
  lag(total, 1)   OVER (PARTITION BY user_id ORDER BY created_at) AS prev_total
FROM app.orders;

JSON / JSONB

-- 提取
SELECT profile->>'company' AS company FROM app.users;
SELECT profile->'address'->>'city' FROM app.users;

-- JSONB 查询索引
CREATE INDEX users_profile_company_gin ON app.users USING gin ((profile->>'company'));

-- 存在性 / 包含
SELECT * FROM app.users WHERE profile ? 'company';              -- 键存在
SELECT * FROM app.users WHERE profile @> '{"role":"admin"}'; -- 包含

-- 更新 JSONB
UPDATE app.users
SET profile = jsonb_set(COALESCE(profile,'{}'::jsonb), '{prefs,theme}', '"dark"', true);

数组

-- 成员资格与包含
SELECT * FROM app.users WHERE 'vip' = ANY(tags);
SELECT * FROM app.users WHERE tags @> ARRAY['beta'];

-- 追加
UPDATE app.users SET tags = array_distinct(tags || ARRAY['vip']);

时间与日期

SELECT now() AT TIME ZONE 'Australia/Melbourne';
SELECT date_trunc('day', created_at) AS d, count(*)
FROM app.users GROUP BY d ORDER BY d;

-- 时间间隔
SELECT now() - interval '7 days';

事务与锁

BEGIN;
UPDATE app.accounts SET balance = balance - 100 WHERE id = 1;
UPDATE app.accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 或 ROLLBACK

-- 隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 检查锁
SELECT * FROM pg_locks l JOIN pg_stat_activity a USING (pid);

角色与权限

-- 创建角色/用户
CREATE ROLE app_user LOGIN PASSWORD 'secret';
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

导入 / 导出

-- 服务器端（需要超级用户或适当权限）
COPY app.users TO '/tmp/users.csv' CSV HEADER;
COPY app.users(email,name) FROM '/tmp/users.csv' CSV HEADER;

-- 客户端（psql）
\copy app.users TO 'users.csv' CSV HEADER
\copy app.users(email,name) FROM 'users.csv' CSV HEADER

性能与可观测性

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;  -- 实际运行时间

-- 统计视图
SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20; -- 需要扩展

-- 维护
VACUUM [FULL] [VERBOSE] table_name;
ANALYZE table_name;
REINDEX TABLE table_name;

启用所需扩展：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE EXTENSION IF NOT EXISTS pg_trgm; -- 三元组搜索

全文搜索（快速）

ALTER TABLE app.docs ADD COLUMN tsv tsvector;
UPDATE app.docs SET tsv = to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));
CREATE INDEX docs_tsv_idx ON app.docs USING gin(tsv);
SELECT id FROM app.docs WHERE tsv @@ plainto_tsquery('english', 'quick brown fox');

有用的 psql 设置

\pset pager off       -- 禁用分页器
\pset null '∅'
\pset format aligned  -- 其他：unaligned, csv
\set ON_ERROR_STOP on
\timing on

实用的目录查询

-- 表大小
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

-- 索引膨胀（近似）
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes ORDER BY idx_scan ASC NULLS FIRST LIMIT 20;

备份 / 恢复

# 逻辑备份
pg_dump -h HOST -U USER -d DB -F c -f db.dump        # 自定义格式
pg_restore -h HOST -U USER -d NEW_DB -j 4 db.dump

# 普通 SQL
pg_dump -h HOST -U USER -d DB > dump.sql
psql -h HOST -U USER -d DB -f dump.sql

复制（高级）

  • WAL 文件从 主节点 → 备节点 流式传输
  • 关键设置：wal_level，max_wal_senders，hot_standby，primary_conninfo
  • 工具：pg_basebackup，standby.signal（PG ≥12）

注意事项与技巧

  • 始终使用 jsonb 而非 json 进行索引和操作。
  • 优先使用 timestamptz（时区感知）。
  • DISTINCT ON 是 Postgres 中“每组前 N 项”的黄金选择。
  • 使用 GENERATED ALWAYS AS IDENTITY 而非 serial。
  • 在生产查询中避免使用 SELECT *。
  • 为选择性过滤和连接键创建索引。
  • 优化前使用 EXPLAIN (ANALYZE) 进行测量。

版本特定功能（≥v12+）

-- 生成标识
CREATE TABLE t (id bigINT GENERATED ALWAYS AS IDENTITY, . ..);

-- 使用部分索引的 UPSERT
CREATE UNIQUE INDEX ON t (key) WHERE is_active;

有用的链接