PostgreSQL 速查表

数据类型速查

类型	说明	示例
SERIAL	自增整数	id SERIAL PRIMARY KEY
BIGSERIAL	自增大整数	id BIGSERIAL PRIMARY KEY
VARCHAR(n)	可变字符串	name VARCHAR(100)
TEXT	无限文本	content TEXT
INTEGER	整数	age INTEGER
BIGINT	大整数	big_num BIGINT
DECIMAL(p,s)	精确小数	price DECIMAL(10,2)
NUMERIC	同 DECIMAL
BOOLEAN	布尔	is_active BOOLEAN
DATE	日期	birth_date DATE
TIMESTAMP	日期时间	created_at TIMESTAMP
TIMESTAMPTZ	带时区时间	updated_at TIMESTAMPTZ
JSON	JSON 数据	data JSON
JSONB	二进制 JSON	data JSONB
UUID	通用唯一标识	session_id UUID
INET	IP 地址	ip_address INET
TEXT[]	字符串数组	tags TEXT[]
INT4RANGE	整数范围	age_range INT4RANGE

约束速查

-- 主键
id SERIAL PRIMARY KEY
PRIMARY KEY (col1, col2)

-- 唯一
UNIQUE (email)
UNIQUE (col1, col2)

-- 非空
NOT NULL

-- 检查
CHECK (age >= 0)
CHECK (status IN ('active', 'inactive'))

-- 外键
REFERENCES users(id)
FOREIGN KEY (user_id) REFERENCES users(id)

-- 默认值
DEFAULT 'pending'
DEFAULT CURRENT_TIMESTAMP
DEFAULT NOW()
DEFAULT nextval('seq')

索引速查

-- 单列索引
CREATE INDEX idx_name ON table(column);

-- 多列索引
CREATE INDEX idx_name ON table(col1, col2);

-- 唯一索引
CREATE UNIQUE INDEX idx_name ON table(column);

-- 表达式索引
CREATE INDEX idx_name ON table(LOWER(email));

-- 部分索引
CREATE INDEX idx_name ON table(column) WHERE status = 'active';

-- 索引类型
CREATE INDEX idx_name ON table USING GIN (tags);
CREATE INDEX idx_name ON table USING GIST (location);

SQL 速查

SELECT

SELECT * FROM table;
SELECT col1, col2 FROM table;
SELECT col AS alias FROM table;
SELECT DISTINCT col FROM table;
SELECT * FROM table WHERE condition;
SELECT * FROM table ORDER BY col ASC|DESC;
SELECT * FROM table LIMIT n OFFSET n;
SELECT * FROM table GROUP BY col;
SELECT * FROM table HAVING condition;
SELECT * FROM table1 JOIN table2 ON condition;
SELECT * FROM table1 LEFT JOIN table2 ON condition;
SELECT * FROM table1 RIGHT JOIN table2 ON condition;
SELECT * FROM table1 FULL OUTER JOIN table2 ON condition;

INSERT

INSERT INTO table (col1, col2) VALUES (val1, val2);
INSERT INTO table (col1, col2) VALUES (val1, val2), (val3, val4);
INSERT INTO table SELECT ...;
INSERT INTO table VALUES (...) ON CONFLICT DO NOTHING;
INSERT INTO table VALUES (...) ON CONFLICT (col) DO UPDATE SET col = EXCLUDED.col;

UPDATE

UPDATE table SET col = val WHERE condition;
UPDATE table SET col1 = val1, col2 = val2 WHERE condition;
UPDATE table SET col = val RETURNING *;

DELETE

DELETE FROM table WHERE condition;
DELETE FROM table;
TRUNCATE TABLE table RESTART IDENTITY CASCADE;

psql 速查

# 连接
psql -U user -d db -h host -p port

# 常用命令
\l                    # 列出数据库
\c db                 # 切换数据库
\dt                   # 列出表
\d table              # 查看表结构
\di                   # 列出索引
\df                   # 列出函数
\dv                   # 列出视图
\du                   # 列出用户
\x                    # 切换扩展模式
\timing               # 启用计时
\i file               # 执行文件
\o file               # 输出到文件
\h SQL                # SQL 帮助
\?                    # psql 帮助

函数速查

字符串

CONCAT(s1, s2)          -- 连接字符串
LENGTH(s)               -- 字符串长度
UPPER(s)                -- 转大写
LOWER(s)                -- 转小写
TRIM(s)                 -- 去空格
SUBSTRING(s, start, len)-- 截取
REPLACE(s, from, to)    -- 替换
SPLIT_PART(s, sep, n)   -- 分割取第N部分
FORMAT(...)             -- 格式化

日期时间

NOW()                   -- 当前时间戳
CURRENT_DATE            -- 当前日期
EXTRACT(YEAR FROM dt)   -- 提取部分
DATE_TRUNC(unit, dt)    -- 截断
TO_CHAR(dt, format)     -- 格式化
TO_DATE(s, format)       -- 字符串转日期
AGE(dt1, dt2)           -- 计算差值

聚合

COUNT(*)                -- 计数
SUM(col)                -- 求和
AVG(col)                -- 平均
MIN(col)                -- 最小
MAX(col)                -- 最大
STRING_AGG(col, sep)    -- 字符串聚合
ARRAY_AGG(col)          -- 数组聚合
JSON_AGG(col)           -- JSON聚合

条件

COALESCE(v1, v2, ...)   -- 返回第一个非NULL
NULLIF(v1, v2)          -- 相等返回NULL
CASE WHEN ... THEN ... END
GREATEST(v1, v2, ...)   -- 最大值
LEAST(v1, v2, ...)      -- 最小值

JSON/JSONB 速查

-- 创建
'{"key": "value"}'::JSON
'{"key": "value"}'::JSONB
'[1, 2, 3]'::JSONB

-- 提取
data->>'key'           -- 返回文本
data->'key'            -- 返回JSON
data->>0               -- 返回数组元素文本
data->0                -- 返回数组元素JSON

-- 操作
data @> '{"key": "value"}'  -- 包含
data <@ '{"key": "value"}'  -- 被包含
data ? 'key'               -- 包含键
data || '{"key": "new"}'   -- 合并
data - 'key'               -- 删除键
data - 0                   -- 删除数组元素

常用配置速查

-- 查看
SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;

-- 设置（会话级）
SET shared_buffers = '256MB';
SET work_mem = '64MB';

-- 设置（永久）
ALTER SYSTEM SET shared_buffers = '256MB';
SELECT pg_reload_conf();

备份恢复速查

# 导出
pg_dump -U user -d db -Fc -f backup.dump
pg_dump -U user -d db -t table -f table.sql

# 导入
pg_restore -U user -d db backup.dump
psql -U user -d db -f table.sql

# 全量
pg_dumpall -U user -f all.sql
psql -U user -f all.sql

系统视图速查

-- 查看表信息
SELECT * FROM pg_tables WHERE schemaname = 'public';

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

-- 查看列信息
SELECT * FROM information_schema.columns WHERE table_name = 'users';

-- 查看约束
SELECT * FROM information_schema.table_constraints WHERE table_name = 'users';

-- 查看权限
SELECT * FROM information_schema.table_privileges WHERE table_name = 'users';

-- 查看统计
SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_stat_user_indexes;
SELECT * FROM pg_stat_statements;