PostgreSQL DML 数据操作

INSERT 插入#

单行写法：插入单行 INSERT INTO <表名> (<列1>, <列2>) VALUES (<值1>, <值2>);

-- 插入一条用户记录
INSERT INTO users (username, email, age) VALUES ('zhangsan', 'zs@example.com', 25);

换行写法：插入多行 INSERT INTO <表名> (<列>) VALUES (<值1>), (<值2>), (<值3>);

-- 批量插入多行
INSERT INTO users (username, email) VALUES
  ('user1', 'u1@example.com'),
  ('user2', 'u2@example.com'),
  ('user3', 'u3@example.com');

单行写法：插入查询结果 INSERT INTO <目标表> SELECT * FROM <源表> [WHERE <条件>];

-- 将活跃用户插入备份表
INSERT INTO active_users_backup SELECT * FROM users WHERE status = 1;

换行写法：ON CONFLICT 冲突处理（upsert） INSERT INTO <表名> (<列>) VALUES (<值>) ON CONFLICT (<列>) DO UPDATE SET <列>=<值>;

-- 冲突时更新
INSERT INTO users (id, username, email) VALUES (1, 'zhangsan', 'new@example.com')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email, updated_at = NOW();

单行写法：冲突时什么都不做 INSERT INTO <表名> (<列>) VALUES (<值>) ON CONFLICT (<列>) DO NOTHING;

-- 冲突时忽略
INSERT INTO users (id, username) VALUES (1, 'zhangsan') ON CONFLICT (id) DO NOTHING;

换行写法：RETURNING 返回数据 INSERT INTO <表名> (<列>) VALUES (<值>) RETURNING <列>;

-- 插入并返回自增ID
INSERT INTO users (username, email) VALUES ('zhangsan', 'zs@example.com')
RETURNING id, username;

换行写法：RETURNING 返回所有列 INSERT INTO <表名> (<列>) VALUES (<值>) RETURNING *;

-- 插入并返回所有列
INSERT INTO users (username, email) VALUES ('zhangsan', 'zs@example.com')
RETURNING *;

UPDATE 更新#

单行写法：更新单列 UPDATE <表名> SET <列>=<值> WHERE <条件>;

-- 更新指定用户年龄
UPDATE users SET age = 26 WHERE id = 1;

单行写法：更新多列 UPDATE <表名> SET <列1>=<值1>, <列2>=<值2> WHERE <条件>;

-- 同时更新多个字段
UPDATE users SET age = 26, status = 2 WHERE id = 1;

单行写法：基于表达式更新 UPDATE <表名> SET <列>=<表达式> WHERE <条件>;

-- 所有用户余额增加 10%
UPDATE users SET balance = balance * 1.10 WHERE status = 1;

换行写法：基于 FROM 关联更新 UPDATE <表1> SET <列>=<值> FROM <表2> WHERE <连接条件>;

-- 关联订单汇总表更新用户余额
UPDATE users SET balance = balance - o.total
FROM (SELECT user_id, SUM(total_amount) AS total FROM orders GROUP BY user_id) o
WHERE users.id = o.user_id;

换行写法：RETURNING 返回更新后数据 UPDATE <表名> SET <列>=<值> WHERE <条件> RETURNING <列>;

-- 更新并返回更新后的数据
UPDATE users SET status = 0 WHERE last_login < '2024-01-01'
RETURNING id, username, status;

换行写法：使用 CASE 条件更新 UPDATE <表名> SET <列> = CASE WHEN <条件> THEN <值> ELSE <默认> END WHERE <条件>;

-- 根据不同状态批量更新
UPDATE users SET status = CASE
  WHEN age < 18 THEN 1
  WHEN age >= 60 THEN 3
  ELSE 2
END WHERE age IS NOT NULL;

DELETE 删除#

单行写法：按条件删除 DELETE FROM <表名> WHERE <条件>;

-- 删除指定用户
DELETE FROM users WHERE id = 1;

换行写法：基于 USING 关联删除 DELETE FROM <表1> USING <表2> WHERE <连接条件>;

-- 删除没有订单的用户
DELETE FROM users
USING orders
WHERE users.id = orders.user_id;

换行写法：基于子查询删除 DELETE FROM <表名> WHERE <列> IN (SELECT <列> FROM <表名> WHERE <条件>);

-- 删除符合条件的关联数据
DELETE FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 0);

换行写法：RETURNING 返回删除数据 DELETE FROM <表名> WHERE <条件> RETURNING <列>;

-- 删除并返回被删除的记录
DELETE FROM users WHERE status = 0 RETURNING id, username;

单行写法：删除所有数据 DELETE FROM <表名>;

-- 删除全表数据
DELETE FROM logs;

UPSERT 操作#

换行写法：基于主键冲突更新 INSERT INTO <表> (<列>) VALUES (<值>) ON CONFLICT ON CONSTRAINT <约束名> DO UPDATE SET <列>=EXCLUDED.<列>;

-- 基于约束名冲突更新
INSERT INTO users (id, username) VALUES (1, 'newname')
ON CONFLICT ON CONSTRAINT users_pkey DO UPDATE SET username = EXCLUDED.username;

换行写法：基于多列冲突更新 INSERT INTO <表> (<列>) VALUES (<值>) ON CONFLICT (<列1>, <列2>) DO UPDATE SET <列>=<值>;

-- 复合唯一键冲突时更新
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 100, 5)
ON CONFLICT (order_id, product_id) DO UPDATE SET quantity = order_items.quantity + EXCLUDED.quantity;

MERGE 命令（PG15+）#

换行写法：MERGE 条件合并 MERGE INTO <目标表> USING <源> ON <条件> WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...;

-- 条件插入或更新
MERGE INTO users AS target
USING (SELECT 1 AS id, 'zhangsan' AS username, 'zs@example.com' AS email) AS source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET email = source.email
WHEN NOT MATCHED THEN INSERT (id, username, email) VALUES (source.id, source.username, source.email);

换行写法：MERGE 带删除（PG17+） MERGE INTO <目标表> USING <源> ON <条件> WHEN MATCHED AND <条件> THEN DELETE;

-- 匹配且满足条件时删除
MERGE INTO users AS t USING inactive_users AS s ON t.id = s.id
WHEN MATCHED AND t.status = 0 THEN DELETE;

事务控制#

单行写法：开启事务 BEGIN; 或 START TRANSACTION;

-- 开启事务
BEGIN;

换行写法：提交事务 COMMIT;

-- 提交事务
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;

单行写法：回滚事务 ROLLBACK;

-- 回滚事务
ROLLBACK;

单行写法：设置保存点 SAVEPOINT <保存点名>;

-- 设置保存点
SAVEPOINT sp1;

单行写法：回滚到保存点 ROLLBACK TO <保存点名>;

-- 回滚到指定保存点
ROLLBACK TO sp1;

单行写法：查看隔离级别 SHOW transaction_isolation;

-- 查看当前事务隔离级别
SHOW transaction_isolation;

单行写法：设置隔离级别 SET TRANSACTION ISOLATION LEVEL <级别>;

-- 设置事务隔离级别为可重复读
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;