PostgreSQL 基本操作
PostgreSQL 是世界上最先进的开源关系型数据库，支持丰富的数据类型和强大的扩展能力。本文档涵盖 PostgreSQL 基本操作、数据类型、函数及 SQL 示例。
1. 基础语法与引号
单引号 —— 字符串常量
在 PostgreSQL 中，单引号用于表示字符串值：
sql
-- 普通字符串
SELECT 'Hello, PostgreSQL';
-- 字符串中包含单引号，使用两个单引号转义
SELECT 'It''s a test';
-- 也可使用美元符号引用（Dollar Quoting），避免转义
SELECT $$It's a test$$;
SELECT $tag$含有'特殊'字符的字符串$tag$;双引号 —— 标识符引用
双引号用于引用标识符（表名、列名等），使其保留大小写或使用保留字：
sql
-- 默认情况下标识符会被转为小写
CREATE TABLE MyTable (id INT); -- 实际创建的表名为 mytable
-- 使用双引号保留大小写
CREATE TABLE "MyTable" (id INT); -- 表名为 MyTable
-- 查询时也必须加双引号
SELECT * FROM "MyTable";
-- 使用保留字作为列名
CREATE TABLE test ("order" INT, "group" TEXT);反引号
PostgreSQL 不支持反引号（`），那是 MySQL 的语法。
2. 数据类型转换
CAST 语法
sql
-- 标准 SQL CAST
SELECT CAST('100' AS INTEGER); -- 100
SELECT CAST('2024-01-15' AS DATE); -- 2024-01-15
SELECT CAST(3.14 AS INTEGER); -- 3（截断）
SELECT CAST(TRUE AS INTEGER); -- 1语法（PostgreSQL 特有）
sql
-- 更简洁的类型转换
SELECT '100'::INTEGER; -- 100
SELECT '3.14'::NUMERIC; -- 3.14
SELECT '2024-01-15'::DATE; -- 2024-01-15
SELECT 1::BOOLEAN; -- true
SELECT 'hello'::VARCHAR(3); -- hel（截断）
-- 复杂转换
SELECT '{"name":"test"}'::JSON;
SELECT '{1,2,3}'::INT[];
SELECT '192.168.1.1'::INET;隐式转换与显式转换
sql
-- 隐式转换：PostgreSQL 自动转换兼容类型
SELECT 1 + '2'; -- 3（字符串自动转为整数）
-- 显式转换更安全，推荐使用
SELECT 1 + CAST('2' AS INTEGER);
-- 转换失败会报错
SELECT 'abc'::INTEGER; -- ERROR转换函数
sql
-- to_number：字符串转数字
SELECT to_number('12,345.67', '99G999D99'); -- 12345.67
-- to_char：数字/日期转字符串
SELECT to_char(12345.6, 'FM999,999.00'); -- 12,345.60
SELECT to_char(NOW(), 'YYYY-MM-DD HH24:MI:SS');
-- to_date：字符串转日期
SELECT to_date('2024-01-15', 'YYYY-MM-DD');
-- to_timestamp：字符串转时间戳
SELECT to_timestamp('2024-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS');3. 布尔类型
布尔值表示
PostgreSQL 支持多种布尔值的表示方式：
sql
-- TRUE 的表示
SELECT TRUE, 'true'::BOOLEAN, 'yes'::BOOLEAN, 'on'::BOOLEAN, '1'::BOOLEAN, 't'::BOOLEAN, 'y'::BOOLEAN;
-- FALSE 的表示
SELECT FALSE, 'false'::BOOLEAN, 'no'::BOOLEAN, 'off'::BOOLEAN, '0'::BOOLEAN, 'f'::BOOLEAN, 'n'::BOOLEAN;
-- NULL（三值逻辑）
SELECT NULL::BOOLEAN;布尔运算
sql
-- AND 运算
SELECT TRUE AND TRUE; -- true
SELECT TRUE AND FALSE; -- false
SELECT TRUE AND NULL; -- NULL
SELECT FALSE AND NULL; -- false（短路求值）
-- OR 运算
SELECT TRUE OR FALSE; -- true
SELECT FALSE OR NULL; -- NULL
SELECT TRUE OR NULL; -- true（短路求值）
-- NOT 运算
SELECT NOT TRUE; -- false
SELECT NOT NULL; -- NULL布尔类型在表中的使用
sql
CREATE TABLE tasks (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
is_completed BOOLEAN DEFAULT FALSE,
is_archived BOOLEAN DEFAULT FALSE
);
INSERT INTO tasks (title, is_completed) VALUES
('学习 PostgreSQL', TRUE),
('写文档', FALSE),
('代码审查', NULL);
-- 查询已完成的任务
SELECT * FROM tasks WHERE is_completed; -- 等价于 is_completed = TRUE
SELECT * FROM tasks WHERE NOT is_completed; -- 未完成（不包含 NULL）
SELECT * FROM tasks WHERE is_completed IS NOT TRUE; -- 未完成（包含 NULL）4. 数值类型
整数类型
| 类型 | 存储大小 | 范围 |
|---|---|---|
SMALLINT / INT2 | 2 字节 | -32,768 ~ 32,767 |
INTEGER / INT / INT4 | 4 字节 | -2,147,483,648 ~ 2,147,483,647 |
BIGINT / INT8 | 8 字节 | -9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807 |
sql
-- 自增序列（推荐方式）
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY, -- 自增 BIGINT
age SMALLINT CHECK (age >= 0),
score INTEGER DEFAULT 0
);
-- SERIAL 类型（语法糖，自动创建序列）
-- SMALLSERIAL → SMALLINT + 序列
-- SERIAL → INTEGER + 序列
-- BIGSERIAL → BIGINT + 序列
-- PostgreSQL 10+ 推荐使用 IDENTITY
CREATE TABLE products (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL
);浮点类型
sql
-- REAL (FLOAT4)：4字节，6位十进制精度
-- DOUBLE PRECISION (FLOAT8)：8字节，15位十进制精度
SELECT 1.0::REAL; -- float4
SELECT 1.0::DOUBLE PRECISION; -- float8
-- 浮点数精度问题
SELECT 0.1::REAL + 0.2::REAL; -- 0.30000001192092896（不精确）
-- 特殊值
SELECT 'Infinity'::DOUBLE PRECISION;
SELECT '-Infinity'::DOUBLE PRECISION;
SELECT 'NaN'::DOUBLE PRECISION;精确数值类型
sql
-- NUMERIC / DECIMAL：任意精度
-- NUMERIC(precision, scale)
SELECT 3.141592653589793::NUMERIC(10, 5); -- 3.14159
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
balance NUMERIC(15, 2) NOT NULL DEFAULT 0.00, -- 最大15位，小数2位
interest_rate NUMERIC(5, 4) -- 如 0.0350
);
-- NUMERIC 无精度限制时可存储任意精度
SELECT 123456789.123456789::NUMERIC; -- 完整保留
-- 精确计算
SELECT 0.1::NUMERIC + 0.2::NUMERIC; -- 0.3（精确）5. 数值类型操作
算术运算
sql
SELECT 10 + 3; -- 13
SELECT 10 - 3; -- 7
SELECT 10 * 3; -- 30
SELECT 10 / 3; -- 3（整数除法）
SELECT 10.0 / 3; -- 3.3333333333333333（浮点除法）
SELECT 10 % 3; -- 1（取模）
SELECT 2 ^ 10; -- 1024（幂运算）
SELECT |/ 144; -- 12（平方根）
SELECT ||/ 27; -- 3（立方根）
SELECT @ -5; -- 5（绝对值）
SELECT 91 & 15; -- 11（位与）
SELECT 32 | 3; -- 35（位或）
SELECT ~1; -- -2（位非）
SELECT 1 << 4; -- 16（左移）
SELECT 16 >> 2; -- 4（右移）数学函数
sql
-- 取整函数
SELECT ceil(4.2); -- 5（向上取整）
SELECT floor(4.8); -- 4（向下取整）
SELECT round(4.567, 2); -- 4.57（四舍五入）
SELECT trunc(4.567, 2); -- 4.56（截断）
-- 其他常用函数
SELECT abs(-10); -- 10
SELECT sign(-5); -- -1
SELECT mod(10, 3); -- 1
SELECT power(2, 10); -- 1024
SELECT sqrt(144); -- 12
SELECT cbrt(27); -- 3
SELECT factorial(5); -- 120
SELECT gcd(12, 8); -- 4（最大公约数，PG 13+）
SELECT lcm(12, 8); -- 24（最小公倍数，PG 13+）
-- 对数与指数
SELECT ln(2.718281828); -- ≈1
SELECT log(100); -- 2（以10为底）
SELECT log(2, 8); -- 3（以2为底）
SELECT exp(1); -- 2.718281828...
-- 三角函数
SELECT pi(); -- 3.141592653589793
SELECT sin(pi() / 2); -- 1
SELECT cos(0); -- 1
SELECT degrees(pi()); -- 180
SELECT radians(180); -- 3.141592653589793
-- 随机数
SELECT random(); -- 0 到 1 之间
SELECT floor(random() * 100 + 1)::INT; -- 1 到 100 之间的随机整数聚合函数
sql
SELECT count(*) FROM users;
SELECT sum(score) FROM users;
SELECT avg(score)::NUMERIC(10,2) FROM users;
SELECT min(score), max(score) FROM users;
SELECT stddev(score), variance(score) FROM users;
-- 统计聚合（PG 支持丰富的统计函数）
SELECT corr(x, y) FROM data_points; -- 相关系数
SELECT regr_slope(y, x) FROM data_points; -- 线性回归斜率6. 字符串类型操作
字符串类型
| 类型 | 说明 |
|---|---|
CHAR(n) | 定长，不足补空格 |
VARCHAR(n) | 变长，有长度限制 |
TEXT | 变长，无长度限制（推荐使用） |
sql
-- TEXT 是 PostgreSQL 推荐的字符串类型
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
content TEXT,
slug TEXT UNIQUE
);字符串操作符
sql
-- 拼接
SELECT 'Hello' || ' ' || 'World'; -- Hello World
SELECT concat('Hello', ' ', 'World'); -- Hello World
SELECT concat_ws('-', '2024', '01', '15'); -- 2024-01-15
-- 长度
SELECT length('Hello'); -- 5
SELECT char_length('你好'); -- 2（字符数）
SELECT octet_length('你好'); -- 6（字节数，UTF-8）
-- 大小写转换
SELECT upper('hello'); -- HELLO
SELECT lower('HELLO'); -- hello
SELECT initcap('hello world'); -- Hello World字符串函数
sql
-- 截取
SELECT substring('Hello World' FROM 7 FOR 5); -- World
SELECT substr('Hello World', 7, 5); -- World
SELECT left('Hello', 3); -- Hel
SELECT right('Hello', 3); -- llo
-- 查找
SELECT position('World' IN 'Hello World'); -- 7
SELECT strpos('Hello World', 'World'); -- 7
-- 替换
SELECT replace('Hello World', 'World', 'PG'); -- Hello PG
SELECT translate('hello', 'helo', 'HELO'); -- HELLO
SELECT overlay('Hello World' PLACING 'PG' FROM 7 FOR 5); -- Hello PG
-- 去除空白
SELECT trim(' Hello '); -- 'Hello'
SELECT ltrim(' Hello'); -- 'Hello'
SELECT rtrim('Hello '); -- 'Hello'
SELECT trim(BOTH 'x' FROM 'xxxHelloxxx'); -- 'Hello'
-- 填充
SELECT lpad('42', 5, '0'); -- 00042
SELECT rpad('Hello', 10, '.'); -- Hello.....
-- 重复与反转
SELECT repeat('Ha', 3); -- HaHaHa
SELECT reverse('Hello'); -- olleH
-- 分割
SELECT split_part('2024-01-15', '-', 2); -- 01
SELECT string_to_array('a,b,c', ','); -- {a,b,c}
SELECT array_to_string(ARRAY['a','b','c'], '-'); -- a-b-c
-- 正则表达式
SELECT 'Hello World' ~ 'World'; -- true（匹配）
SELECT 'Hello World' ~* 'world'; -- true（不区分大小写）
SELECT regexp_match('abc123', '(\d+)'); -- {123}
SELECT regexp_matches('a1b2c3', '(\d)', 'g'); -- {1},{2},{3}（全局匹配）
SELECT regexp_replace('Hello 123 World', '\d+', 'XXX', 'g'); -- Hello XXX World
SELECT regexp_split_to_table('one,two,three', ','); -- 拆成多行
SELECT regexp_split_to_array('one,two,three', ','); -- {one,two,three}模式匹配
sql
-- LIKE
SELECT 'Hello' LIKE 'H%'; -- true
SELECT 'Hello' LIKE '_ello'; -- true
SELECT 'Hello' ILIKE 'hello'; -- true（不区分大小写）
-- SIMILAR TO（SQL标准正则）
SELECT 'abc' SIMILAR TO '(a|b)%'; -- true
-- POSIX 正则
SELECT 'Hello123' ~ '^\w+\d+$'; -- true格式化
sql
-- format 函数（类似 printf）
SELECT format('Hello, %s! You are %s years old.', 'Alice', 30);
-- Hello, Alice! You are 30 years old.
SELECT format('INSERT INTO %I VALUES (%L)', 'my_table', 'test''value');
-- INSERT INTO my_table VALUES ('test''value')
-- %I：标识符引用 %L：字面量引用（防SQL注入）7. 日期类型操作
日期时间类型
| 类型 | 存储大小 | 说明 | 示例 |
|---|---|---|---|
DATE | 4 字节 | 仅日期 | 2024-01-15 |
TIME | 8 字节 | 仅时间（无时区） | 14:30:00 |
TIMETZ | 12 字节 | 时间（带时区） | 14:30:00+08 |
TIMESTAMP | 8 字节 | 日期+时间（无时区） | 2024-01-15 14:30:00 |
TIMESTAMPTZ | 8 字节 | 日期+时间（带时区，推荐） | 2024-01-15 14:30:00+08 |
INTERVAL | 16 字节 | 时间间隔 | 1 year 2 months 3 days |
获取当前时间
sql
SELECT NOW(); -- 当前时间戳（带时区）
SELECT CURRENT_TIMESTAMP; -- 同 NOW()
SELECT CURRENT_DATE; -- 当前日期
SELECT CURRENT_TIME; -- 当前时间
SELECT clock_timestamp(); -- 真实当前时间（每次调用不同）
SELECT statement_timestamp(); -- 语句开始时间
SELECT transaction_timestamp(); -- 事务开始时间
SELECT timeofday(); -- 字符串形式的当前时间日期运算
sql
-- 日期加减
SELECT DATE '2024-01-15' + INTEGER '10'; -- 2024-01-25
SELECT DATE '2024-01-15' + INTERVAL '1 month'; -- 2024-02-15
SELECT DATE '2024-03-15' - DATE '2024-01-15'; -- 60（天数差）
-- 时间戳运算
SELECT TIMESTAMP '2024-01-15 10:00:00' + INTERVAL '2 hours 30 minutes';
SELECT NOW() - INTERVAL '7 days'; -- 7天前
SELECT NOW() + INTERVAL '1 year 3 months'; -- 1年3个月后
-- 间隔乘除
SELECT INTERVAL '1 hour' * 3; -- 03:00:00
SELECT INTERVAL '1 day' / 24; -- 01:00:00日期提取
sql
-- EXTRACT / DATE_PART
SELECT EXTRACT(YEAR FROM TIMESTAMP '2024-01-15 10:30:45'); -- 2024
SELECT EXTRACT(MONTH FROM NOW());
SELECT EXTRACT(DAY FROM NOW());
SELECT EXTRACT(HOUR FROM NOW());
SELECT EXTRACT(DOW FROM DATE '2024-01-15'); -- 1（周一，0=周日）
SELECT EXTRACT(DOY FROM DATE '2024-01-15'); -- 15（一年中第15天）
SELECT EXTRACT(WEEK FROM DATE '2024-01-15'); -- 3（ISO周数）
SELECT EXTRACT(EPOCH FROM NOW()); -- Unix时间戳（秒）
-- DATE_PART（与 EXTRACT 功能相同）
SELECT date_part('year', NOW());
SELECT date_part('quarter', NOW());
-- DATE_TRUNC（截断到指定精度）
SELECT date_trunc('month', TIMESTAMP '2024-01-15 10:30:45'); -- 2024-01-01 00:00:00
SELECT date_trunc('hour', NOW());
SELECT date_trunc('week', NOW());日期格式化
sql
-- to_char 格式化
SELECT to_char(NOW(), 'YYYY-MM-DD'); -- 2024-01-15
SELECT to_char(NOW(), 'YYYY年MM月DD日'); -- 2024年01月15日
SELECT to_char(NOW(), 'HH24:MI:SS'); -- 14:30:45
SELECT to_char(NOW(), 'Day, DD Month YYYY'); -- Monday, 15 January 2024
SELECT to_char(NOW(), 'YYYY-MM-DD HH24:MI:SS TZ'); -- 带时区
-- 常用格式符
-- YYYY：4位年 MM：月 DD：日
-- HH24：24小时 HH12：12小时
-- MI：分钟 SS：秒
-- TZ：时区缩写 OF：时区偏移
-- Day：星期全名 Mon：月份缩写
-- Q：季度 WW：周数时区操作
sql
-- 查看/设置时区
SHOW timezone;
SET timezone = 'Asia/Shanghai';
-- 时区转换
SELECT NOW() AT TIME ZONE 'UTC';
SELECT NOW() AT TIME ZONE 'America/New_York';
SELECT TIMESTAMP '2024-01-15 10:00:00' AT TIME ZONE 'Asia/Shanghai';
-- 生成时间序列
SELECT generate_series(
'2024-01-01'::DATE,
'2024-01-31'::DATE,
'1 day'::INTERVAL
) AS date_series;AGE 函数
sql
-- 计算两个日期之间的差异
SELECT age(TIMESTAMP '2024-01-15', TIMESTAMP '1990-06-20');
-- 结果：33 years 6 mons 25 days
SELECT age(TIMESTAMP '2024-01-15'); -- 从当前日期到指定日期的差8. JSON 类型操作
JSON vs JSONB
| 特性 | JSON | JSONB |
|---|---|---|
| 存储格式 | 原始文本 | 二进制 |
| 写入速度 | 快 | 较慢（需解析） |
| 读取速度 | 慢 | 快 |
| 支持索引 | 否 | 是（GIN索引） |
| 保留格式 | 是 | 否（重新格式化） |
| 去重 | 否 | 是（保留最后一个） |
sql
-- 推荐使用 JSONB
CREATE TABLE events (
id SERIAL PRIMARY KEY,
data JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW()
);JSON 创建
sql
-- 直接赋值
SELECT '{"name": "Alice", "age": 30}'::JSONB;
-- 构建函数
SELECT json_build_object('name', 'Alice', 'age', 30);
SELECT jsonb_build_object('name', 'Alice', 'scores', ARRAY[90,85,95]);
SELECT json_build_array(1, 2, 'three', TRUE);
-- 聚合为 JSON
SELECT json_agg(name) FROM users; -- ["Alice","Bob"]
SELECT jsonb_object_agg(key, value) FROM settings; -- {"k1":"v1","k2":"v2"}
-- 行转 JSON
SELECT row_to_json(t) FROM (SELECT id, name FROM users) t;
SELECT to_jsonb(t) FROM (SELECT id, name FROM users) t;JSON 查询操作符
sql
-- -> 获取 JSON 对象字段（返回 JSON）
SELECT '{"name":"Alice","age":30}'::JSONB -> 'name'; -- "Alice"
-- ->> 获取 JSON 对象字段（返回 TEXT）
SELECT '{"name":"Alice","age":30}'::JSONB ->> 'name'; -- Alice
-- -> 获取 JSON 数组元素（返回 JSON）
SELECT '[1,2,3]'::JSONB -> 1; -- 2
-- ->> 获取 JSON 数组元素（返回 TEXT）
SELECT '[1,2,3]'::JSONB ->> 1; -- 2
-- #> 按路径获取（返回 JSON）
SELECT '{"a":{"b":{"c":1}}}'::JSONB #> '{a,b,c}'; -- 1
-- #>> 按路径获取（返回 TEXT）
SELECT '{"a":{"b":{"c":1}}}'::JSONB #>> '{a,b,c}'; -- 1JSONB 包含与存在操作符
sql
-- @> 包含（左包含右）
SELECT '{"a":1,"b":2}'::JSONB @> '{"a":1}'; -- true
-- <@ 被包含
SELECT '{"a":1}'::JSONB <@ '{"a":1,"b":2}'::JSONB; -- true
-- ? 键存在
SELECT '{"a":1,"b":2}'::JSONB ? 'a'; -- true
-- ?| 任一键存在
SELECT '{"a":1,"b":2}'::JSONB ?| ARRAY['a','c']; -- true
-- ?& 所有键都存在
SELECT '{"a":1,"b":2}'::JSONB ?& ARRAY['a','b']; -- trueJSONB 修改操作
sql
-- || 合并
SELECT '{"a":1}'::JSONB || '{"b":2}'::JSONB; -- {"a":1,"b":2}
SELECT '{"a":1}'::JSONB || '{"a":2}'::JSONB; -- {"a":2}（覆盖）
-- - 删除键
SELECT '{"a":1,"b":2,"c":3}'::JSONB - 'b'; -- {"a":1,"c":3}
-- - 删除数组元素（按索引）
SELECT '["a","b","c"]'::JSONB - 1; -- ["a","c"]
-- #- 按路径删除
SELECT '{"a":{"b":1,"c":2}}'::JSONB #- '{a,b}'; -- {"a":{"c":2}}
-- jsonb_set 设置嵌套值
SELECT jsonb_set('{"a":{"b":1}}'::JSONB, '{a,b}', '2'); -- {"a":{"b":2}}
SELECT jsonb_set('{"a":1}'::JSONB, '{b}', '"new"', TRUE); -- {"a":1,"b":"new"}
-- jsonb_insert 插入
SELECT jsonb_insert('{"a":[1,2]}'::JSONB, '{a,1}', '99'); -- {"a":[1,99,2]}
-- jsonb_strip_nulls 移除 null 值
SELECT jsonb_strip_nulls('{"a":1,"b":null,"c":3}'::JSONB); -- {"a":1,"c":3}JSONB 遍历与转换
sql
-- 遍历键值对
SELECT * FROM jsonb_each('{"a":1,"b":"hello"}'::JSONB);
-- key | value
-- a | 1
-- b | "hello"
SELECT * FROM jsonb_each_text('{"a":1,"b":"hello"}'::JSONB);
-- key | value
-- a | 1
-- b | hello
-- 获取所有键
SELECT jsonb_object_keys('{"a":1,"b":2,"c":3}'::JSONB);
-- 数组遍历
SELECT * FROM jsonb_array_elements('[1,2,3]'::JSONB);
SELECT * FROM jsonb_array_elements_text('["a","b","c"]'::JSONB);
-- 数组长度
SELECT jsonb_array_length('[1,2,3]'::JSONB); -- 3
-- JSON 类型判断
SELECT jsonb_typeof('123'::JSONB); -- number
SELECT jsonb_typeof('"hello"'::JSONB); -- string
SELECT jsonb_typeof('true'::JSONB); -- boolean
SELECT jsonb_typeof('null'::JSONB); -- null
SELECT jsonb_typeof('{"a":1}'::JSONB); -- object
SELECT jsonb_typeof('[1,2]'::JSONB); -- arrayJSON 路径查询（PostgreSQL 12+）
sql
-- jsonb_path_query：JSONPath 查询
SELECT jsonb_path_query(
'{"users":[{"name":"Alice","age":30},{"name":"Bob","age":25}]}'::JSONB,
'$.users[*] ? (@.age > 26)'
);
-- {"name":"Alice","age":30}
SELECT jsonb_path_exists(
'{"a":1}'::JSONB,
'$.a'
); -- true
-- jsonb_path_query_array
SELECT jsonb_path_query_array(
'[1,2,3,4,5]'::JSONB,
'$[*] ? (@ > 3)'
); -- [4, 5]JSONB 索引
sql
-- GIN 索引（推荐）
CREATE INDEX idx_events_data ON events USING GIN (data);
-- 支持 @>, ?, ?|, ?& 操作符
SELECT * FROM events WHERE data @> '{"type":"click"}';
-- jsonb_path_ops（更小的索引，仅支持 @>）
CREATE INDEX idx_events_data_path ON events USING GIN (data jsonb_path_ops);
-- 表达式索引（针对特定字段）
CREATE INDEX idx_events_type ON events USING BTREE ((data ->> 'type'));9. IP 类型操作
IP 地址类型
| 类型 | 说明 |
|---|---|
INET | IP 地址（可带子网掩码） |
CIDR | IP 网络地址（严格的网络地址） |
MACADDR | MAC 地址 |
MACADDR8 | MAC 地址（EUI-64 格式） |
sql
CREATE TABLE access_logs (
id SERIAL PRIMARY KEY,
client_ip INET NOT NULL,
network CIDR,
device_mac MACADDR
);
INSERT INTO access_logs (client_ip, network, device_mac) VALUES
('192.168.1.100/24', '192.168.1.0/24', '08:00:2b:01:02:03'),
('10.0.0.50', '10.0.0.0/8', '08:00:2b:04:05:06'),
('2001:db8::1/64', '2001:db8::/32', NULL);IP 地址操作
sql
-- 基本操作
SELECT inet '192.168.1.100' + 10; -- 192.168.1.110
SELECT inet '192.168.1.100' - inet '192.168.1.1'; -- 99
SELECT host('192.168.1.100/24'::INET); -- 192.168.1.100
SELECT masklen('192.168.1.100/24'::INET); -- 24
SELECT netmask('192.168.1.100/24'::INET); -- 255.255.255.0
SELECT network('192.168.1.100/24'::INET); -- 192.168.1.0/24
SELECT broadcast('192.168.1.100/24'::INET); -- 192.168.1.255/24
SELECT hostmask('192.168.1.0/24'::INET); -- 0.0.0.255
SELECT family('192.168.1.1'::INET); -- 4（IPv4）
SELECT family('::1'::INET); -- 6（IPv6）
-- 包含关系
SELECT inet '192.168.1.0/24' >> inet '192.168.1.100'; -- true（网络包含主机）
SELECT inet '192.168.1.100' << inet '192.168.1.0/24'; -- true（主机属于网络）
SELECT inet '192.168.1.0/24' >>= inet '192.168.1.0/24'; -- true（包含或等于）
-- 位运算
SELECT inet '192.168.1.100' & inet '255.255.255.0'; -- 192.168.1.0
SELECT inet '192.168.1.100' | inet '0.0.0.255'; -- 192.168.1.255
SELECT ~ inet '0.0.0.0'; -- 255.255.255.255利用 IP 类型进行访问控制
sql
-- 查找来自特定子网的访问
SELECT * FROM access_logs WHERE client_ip << inet '192.168.1.0/24';
-- IP 地址排序（自然排序，非字符串排序）
SELECT client_ip FROM access_logs ORDER BY client_ip;
-- GiST 索引支持包含查询
CREATE INDEX idx_logs_ip ON access_logs USING GIST (client_ip inet_ops);10. 枚举类型操作
创建和使用枚举
sql
-- 创建枚举类型
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
-- 在表中使用
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_name TEXT NOT NULL,
status order_status DEFAULT 'pending',
created_at TIMESTAMPTZ DEFAULT NOW()
);
INSERT INTO orders (customer_name, status) VALUES
('Alice', 'pending'),
('Bob', 'shipped'),
('Charlie', 'delivered');
-- 枚举自动有序，可以比较
SELECT * FROM orders WHERE status >= 'shipped'; -- shipped, delivered
SELECT * FROM orders ORDER BY status; -- 按枚举定义顺序排序枚举操作
sql
-- 查看枚举值
SELECT enum_range(NULL::order_status);
-- {pending,processing,shipped,delivered,cancelled}
-- 第一个和最后一个值
SELECT enum_first(NULL::order_status); -- pending
SELECT enum_last(NULL::order_status); -- cancelled
-- 枚举值范围
SELECT enum_range('processing'::order_status, 'delivered'::order_status);
-- {processing,shipped,delivered}修改枚举
sql
-- 添加新值
ALTER TYPE order_status ADD VALUE 'returned'; -- 添加到末尾
ALTER TYPE order_status ADD VALUE 'confirmed' AFTER 'pending'; -- 在 pending 之后
ALTER TYPE order_status ADD VALUE 'draft' BEFORE 'pending'; -- 在 pending 之前
ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'returned'; -- 避免重复添加
-- 重命名枚举值（PostgreSQL 10+）
ALTER TYPE order_status RENAME VALUE 'cancelled' TO 'canceled';
-- 注意：PostgreSQL 不支持直接删除枚举值！
-- 需要重新创建类型来删除值
-- 查询系统表获取枚举信息
SELECT enumlabel, enumsortorder
FROM pg_enum
WHERE enumtypid = 'order_status'::regtype
ORDER BY enumsortorder;11. 复合类型操作
创建复合类型
sql
-- 显式创建复合类型
CREATE TYPE address AS (
street TEXT,
city TEXT,
state TEXT,
zip_code TEXT,
country TEXT
);
CREATE TYPE full_name AS (
first_name TEXT,
last_name TEXT
);
-- 每个表自动有对应的复合类型
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name full_name,
home_address address,
work_address address,
salary NUMERIC(10,2)
);复合类型操作
sql
-- 使用 ROW 构造器插入
INSERT INTO employees (name, home_address, salary) VALUES
(ROW('Alice', 'Smith'),
ROW('123 Main St', 'Seattle', 'WA', '98101', 'US'),
85000.00);
-- 也可以用圆括号
INSERT INTO employees (name, home_address, salary) VALUES
(('Bob', 'Johnson'),
('456 Oak Ave', 'Portland', 'OR', '97201', 'US'),
92000.00);
-- 访问复合类型字段（注意圆括号）
SELECT (name).first_name, (name).last_name FROM employees;
SELECT (home_address).city, (home_address).state FROM employees;
-- 表别名方式
SELECT e.name, (e.home_address).city
FROM employees e
WHERE (e.home_address).state = 'WA';
-- 更新复合类型字段
UPDATE employees SET name.first_name = 'Robert' WHERE id = 2;
UPDATE employees SET home_address = ROW('789 New St', 'Seattle', 'WA', '98102', 'US') WHERE id = 1;
-- 比较复合类型（逐字段比较）
SELECT * FROM employees WHERE name = ROW('Alice', 'Smith')::full_name;
SELECT * FROM employees WHERE name > ROW('Alice', 'Smith')::full_name;
-- 复合类型是否为 NULL
SELECT * FROM employees WHERE name IS NOT NULL;12. 数组类型操作
创建数组
sql
-- 数组声明
CREATE TABLE student_scores (
id SERIAL PRIMARY KEY,
name TEXT,
scores INTEGER[], -- 一维数组
matrix INTEGER[][], -- 多维数组
tags TEXT[] DEFAULT '{}' -- 默认空数组
);
-- 数组常量语法
INSERT INTO student_scores (name, scores, tags) VALUES
('Alice', '{90, 85, 95, 88}', '{"math","science"}'),
('Bob', ARRAY[78, 82, 90, 85], ARRAY['english','history']),
('Charlie', '{95, 92, 88, 91}', '{"math","english","art"}');数组访问
sql
-- 下标访问（从1开始）
SELECT scores[1] FROM student_scores WHERE name = 'Alice'; -- 90
SELECT scores[2:3] FROM student_scores WHERE name = 'Alice'; -- {85,95}（切片）
-- 数组长度
SELECT array_length(scores, 1) FROM student_scores; -- 4（第1维长度）
SELECT cardinality(scores) FROM student_scores; -- 4（总元素数）
SELECT array_dims(scores) FROM student_scores; -- [1:4]
-- 数组上下界
SELECT array_lower(scores, 1), array_upper(scores, 1)
FROM student_scores WHERE name = 'Alice'; -- 1, 4数组操作符
sql
-- 拼接
SELECT ARRAY[1,2] || ARRAY[3,4]; -- {1,2,3,4}
SELECT ARRAY[1,2] || 3; -- {1,2,3}
SELECT 0 || ARRAY[1,2]; -- {0,1,2}
-- 包含
SELECT ARRAY[1,2,3] @> ARRAY[1,3]; -- true（左包含右）
SELECT ARRAY[1,3] <@ ARRAY[1,2,3]; -- true（左被右包含）
-- 重叠（有共同元素）
SELECT ARRAY[1,2,3] && ARRAY[3,4,5]; -- true
-- 相等
SELECT ARRAY[1,2,3] = ARRAY[1,2,3]; -- true
SELECT ARRAY[1,2,3] <> ARRAY[3,2,1]; -- true（顺序不同）数组函数
sql
-- 追加/前置
SELECT array_append(ARRAY[1,2,3], 4); -- {1,2,3,4}
SELECT array_prepend(0, ARRAY[1,2,3]); -- {0,1,2,3}
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]); -- {1,2,3,4}
-- 查找
SELECT array_position(ARRAY['a','b','c'], 'b'); -- 2
SELECT array_positions(ARRAY[1,2,1,3,1], 1); -- {1,3,5}
-- 删除
SELECT array_remove(ARRAY[1,2,3,2], 2); -- {1,3}
-- 替换
SELECT array_replace(ARRAY[1,2,3,2], 2, 99); -- {1,99,3,99}
-- 去重（需要排序）
SELECT ARRAY(SELECT DISTINCT unnest(ARRAY[1,2,2,3,3,3])); -- {1,2,3}
-- 展开为行
SELECT unnest(ARRAY['a','b','c']); -- a, b, c（3行）
-- 行聚合为数组
SELECT array_agg(name) FROM student_scores; -- {Alice,Bob,Charlie}
SELECT array_agg(name ORDER BY name) FROM student_scores;
-- 字符串与数组转换
SELECT string_to_array('1,2,3', ','); -- {1,2,3}
SELECT array_to_string(ARRAY[1,2,3], ','); -- 1,2,3
-- 生成数组
SELECT ARRAY(SELECT generate_series(1, 10)); -- {1,2,3,...,10}数组比较
sql
-- 数组逐元素比较（字典序）
SELECT ARRAY[1,2,3] > ARRAY[1,2,2]; -- true
SELECT ARRAY[1,2] > ARRAY[1,2,3]; -- false（更短的数组更小）
-- ANY / ALL 操作符
SELECT 2 = ANY(ARRAY[1,2,3]); -- true（任一匹配）
SELECT 5 > ALL(ARRAY[1,2,3]); -- true（全部满足）
-- 在 WHERE 中使用
SELECT * FROM student_scores WHERE 90 = ANY(scores);
SELECT * FROM student_scores WHERE 'math' = ANY(tags);数组索引
sql
-- GIN 索引（支持包含、重叠等查询）
CREATE INDEX idx_tags ON student_scores USING GIN (tags);
-- 使用 @> 查询（利用索引）
SELECT * FROM student_scores WHERE tags @> ARRAY['math'];
SELECT * FROM student_scores WHERE scores @> ARRAY[90];13. UUID 类型
sql
-- PostgreSQL 13+ 内置 gen_random_uuid()
-- 早期版本需要 uuid-ossp 扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id INTEGER NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
INSERT INTO sessions (user_id) VALUES (1);
SELECT * FROM sessions;
-- id: a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
-- UUID 生成函数
SELECT gen_random_uuid(); -- v4 随机 UUID
SELECT uuid_generate_v1(); -- v1 基于时间
SELECT uuid_generate_v4(); -- v4 随机
SELECT uuid_generate_v5(uuid_ns_url(), 'https://example.com'); -- v5 基于名称
-- UUIDv7（PostgreSQL 17+）
SELECT uuidv7(); -- 时间排序的 UUID14. 范围类型
范围类型概览
| 类型 | 描述 |
|---|---|
INT4RANGE | 整数范围 |
INT8RANGE | 大整数范围 |
NUMRANGE | 数值范围 |
TSRANGE | 时间戳范围（无时区） |
TSTZRANGE | 时间戳范围（带时区） |
DATERANGE | 日期范围 |
sql
-- 范围表示法
SELECT '[1, 10]'::INT4RANGE; -- [1,11)（含1含10，内部转为半开）
SELECT '[1, 10)'::INT4RANGE; -- [1,10)（含1不含10）
SELECT '(1, 10)'::INT4RANGE; -- [2,10)（不含1不含10）
SELECT 'empty'::INT4RANGE; -- empty
-- 实际应用：预订系统
CREATE TABLE reservations (
id SERIAL PRIMARY KEY,
room_id INTEGER NOT NULL,
during TSTZRANGE NOT NULL,
guest_name TEXT NOT NULL,
-- 排除约束：同一房间不能有重叠预订
EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);
INSERT INTO reservations (room_id, during, guest_name) VALUES
(101, '[2024-06-01, 2024-06-05)', 'Alice'),
(101, '[2024-06-05, 2024-06-08)', 'Bob'); -- OK
-- (101, '[2024-06-04, 2024-06-07)', 'Charlie'); -- 会报错（与 Alice 重叠）范围操作符
sql
-- 包含元素
SELECT '[1,10)'::INT4RANGE @> 5; -- true
SELECT 5 <@ '[1,10)'::INT4RANGE; -- true
-- 包含范围
SELECT '[1,10)'::INT4RANGE @> '[3,7)'::INT4RANGE; -- true
-- 重叠
SELECT '[1,5)'::INT4RANGE && '[3,8)'::INT4RANGE; -- true
-- 相邻
SELECT '[1,5)'::INT4RANGE -|- '[5,10)'::INT4RANGE; -- true
-- 并集、交集、差集
SELECT '[1,5)'::INT4RANGE + '[3,8)'::INT4RANGE; -- [1,8)
SELECT '[1,5)'::INT4RANGE * '[3,8)'::INT4RANGE; -- [3,5)
SELECT '[1,10)'::INT4RANGE - '[3,5)'::INT4RANGE; -- 不支持（结果不连续会报错）
-- 范围函数
SELECT lower('[1,10)'::INT4RANGE); -- 1
SELECT upper('[1,10)'::INT4RANGE); -- 10
SELECT isempty('empty'::INT4RANGE); -- true
SELECT lower_inc('[1,10)'::INT4RANGE); -- true（下界包含）
SELECT upper_inc('[1,10)'::INT4RANGE); -- false（上界不包含）多重范围（PostgreSQL 14+）
sql
-- 多重范围：多个不相交范围的集合
SELECT '{[1,3), [5,7)}'::INT4MULTIRANGE;
-- 操作与范围类型类似
SELECT '{[1,3), [5,7)}'::INT4MULTIRANGE @> 6; -- true
SELECT '{[1,3), [5,7)}'::INT4MULTIRANGE && '[2,6)'::INT4RANGE; -- true15. 位串类型
sql
-- BIT(n)：定长位串
-- BIT VARYING(n) / VARBIT(n)：变长位串
SELECT B'10110'; -- 10110
SELECT '10110'::BIT(8); -- 10110000（补0）
-- 位运算
SELECT B'1010' & B'1100'; -- 1000（AND）
SELECT B'1010' | B'1100'; -- 1110（OR）
SELECT B'1010' # B'1100'; -- 0110（XOR）
SELECT ~ B'1010'; -- 0101（NOT）
SELECT B'1010' << 1; -- 0100（左移）
SELECT B'1010' >> 1; -- 0101（右移）
-- 与整数转换
SELECT 42::BIT(8); -- 00101010
SELECT B'00101010'::INTEGER; -- 4216. XML 类型
sql
-- XML 类型存储
CREATE TABLE xml_docs (
id SERIAL PRIMARY KEY,
content XML
);
INSERT INTO xml_docs (content) VALUES
('<book><title>PostgreSQL指南</title><author>Alice</author></book>');
-- XML 函数
SELECT xmlparse(DOCUMENT '<root><item>1</item></root>');
SELECT xmlserialize(DOCUMENT '<root/>'::XML AS TEXT);
-- XPath 查询
SELECT xpath('/book/title/text()', content) FROM xml_docs;
-- {PostgreSQL指南}
SELECT xpath_exists('/book/author', content) FROM xml_docs;
-- true
-- 生成 XML
SELECT xmlelement(NAME book,
xmlelement(NAME title, 'PostgreSQL Guide'),
xmlelement(NAME year, 2024)
);
-- <book><title>PostgreSQL Guide</title><year>2024</year></book>
SELECT xmlforest(name, age) FROM users;
-- <name>Alice</name><age>30</age>
-- 表数据导出为 XML
SELECT table_to_xml('users', TRUE, FALSE, '');17. 字节类型 (bytea)
sql
-- 存储二进制数据
CREATE TABLE files (
id SERIAL PRIMARY KEY,
filename TEXT NOT NULL,
data BYTEA,
mime_type TEXT
);
-- 十六进制格式输入
INSERT INTO files (filename, data) VALUES
('test.bin', '\x48656c6c6f'); -- Hello 的十六进制
-- 转义格式
INSERT INTO files (filename, data) VALUES
('test.txt', E'Hello\\000World'); -- 包含 NULL 字节
-- 字节操作
SELECT length('\x48656c6c6f'::BYTEA); -- 5
SELECT get_byte('\x48656c6c6f'::BYTEA, 0); -- 72 (H)
SELECT set_byte('\x48656c6c6f'::BYTEA, 0, 74); -- \x4a656c6c6f (Jello)
SELECT encode('\x48656c6c6f'::BYTEA, 'base64'); -- SGVsbG8=
SELECT decode('SGVsbG8=', 'base64'); -- \x48656c6c6f
-- MD5/SHA 哈希
SELECT md5('Hello'); -- 8b1a9953c4611296a827abf8c47804d7
SELECT encode(sha256('Hello'::BYTEA), 'hex');18. 域类型 (Domain)
sql
-- 域是带约束的自定义类型
CREATE DOMAIN email AS TEXT
CHECK (VALUE ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');
CREATE DOMAIN positive_int AS INTEGER
CHECK (VALUE > 0);
CREATE DOMAIN percentage AS NUMERIC(5,2)
CHECK (VALUE >= 0 AND VALUE <= 100);
-- 使用域类型
CREATE TABLE contacts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email_addr email NOT NULL,
age positive_int,
score percentage
);
INSERT INTO contacts (name, email_addr, age, score) VALUES
('Alice', 'alice@example.com', 30, 95.50); -- OK
-- 以下会报错
-- INSERT INTO contacts (name, email_addr) VALUES ('Bob', 'invalid-email');
-- INSERT INTO contacts (name, email_addr, age) VALUES ('Bob', 'bob@test.com', -5);
-- 修改域约束
ALTER DOMAIN percentage DROP CONSTRAINT percentage_check;
ALTER DOMAIN percentage ADD CONSTRAINT percentage_check CHECK (VALUE >= 0 AND VALUE <= 100);19. 常用 DDL 操作
数据库操作
sql
-- 创建数据库
CREATE DATABASE mydb
WITH ENCODING 'UTF8'
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TEMPLATE template0
OWNER myuser;
-- 修改数据库
ALTER DATABASE mydb SET timezone = 'Asia/Shanghai';
ALTER DATABASE mydb RENAME TO newdb;
ALTER DATABASE mydb OWNER TO newuser;
-- 删除数据库
DROP DATABASE IF EXISTS mydb;Schema 操作
sql
-- 创建 Schema
CREATE SCHEMA IF NOT EXISTS app;
CREATE SCHEMA auth AUTHORIZATION myuser;
-- 设置搜索路径
SET search_path TO app, public;
SHOW search_path;
-- 在 Schema 中创建对象
CREATE TABLE app.users (id SERIAL PRIMARY KEY, name TEXT);表操作
sql
-- 创建表
CREATE TABLE IF NOT EXISTS users (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email TEXT NOT NULL,
age SMALLINT CHECK (age >= 0 AND age <= 150),
status TEXT DEFAULT 'active',
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- 添加列
ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users ADD COLUMN bio TEXT DEFAULT '';
-- 修改列
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'inactive';
ALTER TABLE users ALTER COLUMN age TYPE INTEGER;
ALTER TABLE users RENAME COLUMN phone TO mobile;
-- 删除列
ALTER TABLE users DROP COLUMN IF EXISTS bio;
-- 添加约束
ALTER TABLE users ADD CONSTRAINT chk_username_length CHECK (length(username) >= 3);
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);
-- 删除约束
ALTER TABLE users DROP CONSTRAINT IF EXISTS chk_username_length;
-- 重命名表
ALTER TABLE users RENAME TO app_users;
-- 删除表
DROP TABLE IF EXISTS app_users CASCADE;
-- 清空表
TRUNCATE TABLE users RESTART IDENTITY CASCADE;索引操作
sql
-- 基本索引
CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_users_username ON users (username);
-- 多列索引
CREATE INDEX idx_users_name_email ON users (username, email);
-- 部分索引
CREATE INDEX idx_active_users ON users (username) WHERE status = 'active';
-- 表达式索引
CREATE INDEX idx_users_lower_email ON users (lower(email));
-- 不同索引类型
CREATE INDEX idx_gin_tags ON posts USING GIN (tags); -- 数组/JSON/全文
CREATE INDEX idx_gist_location ON places USING GiST (location); -- 几何/范围/IP
CREATE INDEX idx_brin_created ON logs USING BRIN (created_at); -- 大表时间序列
-- 并发创建索引（不锁表）
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
-- 删除索引
DROP INDEX IF EXISTS idx_users_email;20. 常用 DML 操作
INSERT
sql
-- 基本插入
INSERT INTO users (username, email, age) VALUES ('alice', 'alice@test.com', 28);
-- 多行插入
INSERT INTO users (username, email, age) VALUES
('bob', 'bob@test.com', 32),
('charlie', 'charlie@test.com', 25),
('diana', 'diana@test.com', 30);
-- 返回插入的数据
INSERT INTO users (username, email) VALUES ('eve', 'eve@test.com')
RETURNING id, username, created_at;
-- 冲突处理（UPSERT）
INSERT INTO users (username, email, age) VALUES ('alice', 'alice_new@test.com', 29)
ON CONFLICT (username) DO UPDATE SET
email = EXCLUDED.email,
age = EXCLUDED.age,
updated_at = NOW();
-- 冲突时忽略
INSERT INTO users (username, email) VALUES ('alice', 'alice@test.com')
ON CONFLICT (username) DO NOTHING;
-- 从查询插入
INSERT INTO user_archive (username, email)
SELECT username, email FROM users WHERE status = 'inactive';
-- COPY 批量导入（高性能）
COPY users (username, email, age) FROM '/tmp/users.csv' WITH CSV HEADER;SELECT
sql
-- 基本查询
SELECT * FROM users WHERE age > 25 ORDER BY created_at DESC LIMIT 10;
-- 别名
SELECT username AS name, age AS user_age FROM users;
-- DISTINCT
SELECT DISTINCT status FROM users;
SELECT DISTINCT ON (status) * FROM users ORDER BY status, created_at DESC;
-- 聚合与分组
SELECT status, count(*), avg(age)::NUMERIC(10,1)
FROM users
GROUP BY status
HAVING count(*) > 5;
-- 窗口函数
SELECT username, age,
row_number() OVER (ORDER BY age DESC) AS rank,
dense_rank() OVER (ORDER BY age DESC) AS dense_rank,
lag(age) OVER (ORDER BY age) AS prev_age,
lead(age) OVER (ORDER BY age) AS next_age,
avg(age) OVER () AS overall_avg,
sum(age) OVER (ORDER BY age ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum
FROM users;
-- CTE (Common Table Expression)
WITH active_users AS (
SELECT * FROM users WHERE status = 'active'
),
user_stats AS (
SELECT count(*) AS cnt, avg(age) AS avg_age FROM active_users
)
SELECT * FROM user_stats;
-- 递归 CTE
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id, 1 AS level
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, s.level + 1
FROM employees e
INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY level, name;
-- LATERAL JOIN
SELECT u.*, recent_orders.*
FROM users u
CROSS JOIN LATERAL (
SELECT * FROM orders o
WHERE o.user_id = u.id
ORDER BY o.created_at DESC
LIMIT 3
) recent_orders;UPDATE
sql
-- 基本更新
UPDATE users SET age = 29, updated_at = NOW() WHERE username = 'alice';
-- 从另一个表更新
UPDATE users u SET
status = 'vip'
FROM orders o
WHERE o.user_id = u.id AND o.total > 10000;
-- 返回更新结果
UPDATE users SET age = age + 1 WHERE username = 'bob'
RETURNING id, username, age;
-- 条件更新
UPDATE users SET status = CASE
WHEN age < 20 THEN 'junior'
WHEN age BETWEEN 20 AND 60 THEN 'senior'
ELSE 'retired'
END;DELETE
sql
-- 基本删除
DELETE FROM users WHERE status = 'inactive';
-- 使用子查询
DELETE FROM users WHERE id IN (
SELECT user_id FROM blacklist
);
-- 返回删除的数据
DELETE FROM users WHERE status = 'banned'
RETURNING *;
-- 使用 USING（类似 UPDATE 的 FROM）
DELETE FROM users u USING blacklist b
WHERE u.email = b.email;MERGE（PostgreSQL 15+）
sql
-- MERGE：合并插入/更新/删除
MERGE INTO users AS target
USING staging_users AS source
ON target.username = source.username
WHEN MATCHED AND source.status = 'deleted' THEN
DELETE
WHEN MATCHED THEN
UPDATE SET email = source.email, age = source.age, updated_at = NOW()
WHEN NOT MATCHED THEN
INSERT (username, email, age) VALUES (source.username, source.email, source.age);附录：PostgreSQL 版本新特性速览
| 版本 | 重要新特性 |
|---|---|
| PG 12 | 生成列、JSONPath、CTE 内联优化 |
| PG 13 | 增量排序、并行 VACUUM、gen_random_uuid() 内置 |
| PG 14 | 多重范围类型、JSON 下标访问、SEARCH/CYCLE 递归 CTE |
| PG 15 | MERGE 语句、jsonlog 日志、公共 schema 权限变更 |
| PG 16 | 逻辑复制增强、并行 FULL OUTER JOIN、ANY_VALUE() 聚合 |
| PG 17 | RETURNING 增强、COPY ... HEADER MATCH、JSON 增强、UUIDv7、增量备份 |
参考资源