PostgreSQL DML — 数据操作语言

DML 是"Data Manipulation Language"的缩写,负责写入和修改表里的数据。它包括三大核心命令:INSERT 插入、UPDATE 更新、DELETE 删除,外加一个 Postgres 特色:RETURNING 返回修改后的行——让你少一次查询。

1. INSERT — 插入数据

INSERT 把新行写入表。基础语法是 INSERT INTO 表 (列...) VALUES (值...)。

-- 基础 INSERT:指定列名 + VALUES
INSERT INTO users (name, email, age)
VALUES ('小明', 'xm@example.com', 25);

-- 不写列名时,必须按表的所有列顺序给值(不推荐,容易出错)
INSERT INTO users VALUES (DEFAULT, '小红', 'xh@example.com', 17);

-- 一次插入多行(性能比多次单条 INSERT 高得多)
INSERT INTO users (name, email, age)
VALUES
    ('小明', 'xm@example.com', 25),
    ('小红', 'xh@example.com', 17),
    ('小张', 'xz@example.com', 30);

-- 用 INSERT ... SELECT 从其他表灌数据
INSERT INTO users_backup (name, email)
SELECT name, email FROM users WHERE created_at < '2025-01-01';

-- Postgres 特色:RETURNING 返回刚插入的行
-- 不用再 SELECT 一次就能拿到自动生成的 id
INSERT INTO users (name, email)
VALUES ('小王', 'xw@example.com')
RETURNING id, name, created_at;
--  id | name |          created_at
-- ----+------+-------------------------------
--   4 | 小王 | 2026-08-05 14:30:00.123456+08

几个关键技巧:

多行插入:一条 INSERT 用逗号分隔多组 VALUES,性能比循环执行 N 次单条 INSERT 高一个数量级。
INSERT ... SELECT:从其他表搬运数据,常用于备份、归档、表迁移。
RETURNING 子句:Postgres 杀器,让 INSERT 直接返回刚插入的行(包括自动生成的 id)。应用代码不用再 SELECT lastval(),也避免了"插入后查 id"的并发问题。

2. UPSERT — 冲突时更新或忽略

"UPSERT"是 UPDATE or INSERT 的缩写,处理"主键/唯一键冲突时怎么办"。Postgres 用 ON CONFLICT 子句优雅实现,比 MySQL 的 ON DUPLICATE KEY UPDATE 更清晰。

-- UPSERT = UPDATE or INSERT(冲突时更新)
-- 用 ON CONFLICT 子句实现,这是 Postgres 的特色

-- 1. 冲突时什么都不做
INSERT INTO users (email, name)
VALUES ('xm@example.com', '小明新')
ON CONFLICT (email) DO NOTHING;
-- 假设 email 有 UNIQUE 约束,重复则跳过

-- 2. 冲突时更新(update 字段用 EXCLUDED 引用"新值")
INSERT INTO users (email, name, age)
VALUES ('xm@example.com', '小明新', 26)
ON CONFLICT (email)
DO UPDATE SET
    name = EXCLUDED.name,
    age  = EXCLUDED.age,
    updated_at = now();
-- EXCLUDED 是 Postgres 的特殊表名,代表"原本要插入但被冲突的那一行"

-- 3. 给整个 UPSERT 加条件(只在满足时才更新)
INSERT INTO users (email, name)
VALUES ('xm@example.com', '小明新')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name
WHERE users.name IS DISTINCT FROM EXCLUDED.name;

-- 4. 多列组合唯一约束的冲突处理
-- 假设有 UNIQUE (user_id, product_id)
INSERT INTO cart (user_id, product_id, qty)
VALUES (1, 100, 1)
ON CONFLICT (user_id, product_id)
DO UPDATE SET qty = cart.qty + EXCLUDED.qty;

UPSERT 的常见应用场景:

计数器:点赞、浏览量,每次点一下 INSERT ... ON CONFLICT DO UPDATE SET count = cart.count + 1。
同步外部数据:从 API 拉数据写库,已存在则更新、不存在则插入。
批量导入:导一份大 CSV,冲突的行自动跳过或更新,不用预先去重。

EXCLUDED 是 Postgres 的特殊关键字,代表"原本要插入但因冲突被排除的那行新值"。在 DO UPDATE SET 里用它引用新数据。

3. UPDATE — 更新数据

UPDATE 修改已有行。语法是 UPDATE 表 SET 列=值 WHERE 条件。

-- UPDATE 修改已有行
UPDATE users SET age = 26 WHERE id = 1;

-- 同时更新多列
UPDATE users
SET age = 26, name = '小明改', updated_at = now()
WHERE id = 1;

-- 不要忘了 WHERE!否则更新全表(灾难!)
-- UPDATE users SET age = 0;  -- 所有人的年龄都变成 0

-- 用子查询更新
UPDATE users
SET age = ages.new_age
FROM (
    SELECT user_id, MAX(age) AS new_age FROM user_logs GROUP BY user_id
) AS ages
WHERE users.id = ages.user_id;

-- 用 RETURNING 返回更新后的行
UPDATE users SET role = 'vip' WHERE age > 30
RETURNING id, name, role;

-- UPDATE FROM 是 Postgres 特色(标准 SQL 写法是 UPDATE ... JOIN)

最重要的警告:写 UPDATE 必须带 WHERE!漏掉 WHERE 会更新整张表,且 Postgres 不会问你"确定吗"。生产环境改数据前先写 SELECT ... WHERE ... 看一眼影响范围,再改成 UPDATE。

Postgres 的 UPDATE ... FROM 是特色,允许在 UPDATE 里直接引用另一张表的数据——标准 SQL 要用 JOIN 子查询实现。

4. DELETE — 删除数据

DELETE 删除行。同样地,必须带 WHERE(除非真的想清空全表)。

-- DELETE 删除行
DELETE FROM users WHERE id = 1;

-- 一定一定带 WHERE!
-- DELETE FROM users;  -- 清空整张表!(走事务,可回滚)

-- 用 RETURNING 返回被删除的行(便于记录审计日志)
DELETE FROM users WHERE age < 18
RETURNING id, name, email;

-- 用子查询或 IN 限定删除范围
DELETE FROM orders
WHERE user_id IN (SELECT id FROM users WHERE deleted_at IS NOT NULL);

-- 用 USING 联表删除(Postgres 特色)
DELETE FROM orders
USING users
WHERE orders.user_id = users.id AND users.deleted_at IS NOT NULL;

-- 软删除 vs 硬删除
--   软删除:加一列 deleted_at TIMESTAMPTZ,标记删除(实际不删)
UPDATE users SET deleted_at = now() WHERE id = 1;
--   查询时过滤已删除
SELECT * FROM users WHERE deleted_at IS NULL;
--   硬删除:真正 DELETE FROM,不可恢复(除非有备份)

软删除 vs 硬删除的取舍:

软删除:加 deleted_at 列,实际不删,只是标记。优点:可恢复、保留审计、外键完整。缺点:查询要加 WHERE deleted_at IS NULL,索引和统计可能受影响。
硬删除:真正 DELETE,空间可被 VACUUM 回收。优点:简单干净。缺点:不可恢复。
金融、医疗等合规要求高的场景几乎都用软删除;日志、临时表适合硬删除。

5. RETURNING — 减少 50% 的二次查询

INSERT/UPDATE/DELETE 都支持 RETURNING 子句,这是 Postgres 区别于 MySQL 的另一大杀器。它能:

插入后立即拿到自动生成的 id(避免再用 SELECT 查一遍)。
更新后拿到新值(避免再 SELECT)。
删除前快照被删的行(便于审计日志)。

ORM 和应用层用 RETURNING 能显著减少数据库往返,提升性能。

6. MERGE — 标准化的 UPSERT(Postgres 15+)

Postgres 15 引入了 SQL 标准的 MERGE 语句,比 ON CONFLICT 更强大——能根据条件分别执行 UPDATE / INSERT / DELETE,适合复杂的数据同步场景:

-- Postgres 15+ 的 MERGE(标准 SQL)
-- 用法:把一张"源表"合并进"目标表"
MERGE INTO users AS target
USING (VALUES ('xm@example.com', '小明新', 26)) AS source(email, name, age)
ON target.email = source.email
WHEN MATCHED THEN
    UPDATE SET name = source.name, age = source.age
WHEN NOT MATCHED THEN
    INSERT (email, name, age) VALUES (source.email, source.name, source.age);

日常 UPSERT 推荐 ON CONFLICT(更简洁),复杂合并场景才用 MERGE。

小结

这一章你掌握了 DML 的四大武器:INSERT 写入、UPDATE 修改、DELETE 删除、UPSERT 冲突处理,以及 Postgres 特色 RETURNING。下一篇我们进入数据库的"灵魂"——SELECT 查询与 JOIN。

← 上一篇 PostgreSQL DDL

下一篇 PostgreSQL SELECT 与 JOIN →

✈️💬