如何在PostgreSQL中用SQL批量插入数据并返回新增行？-SQL-PHP中文网

postgresql的insert ... returning是一次性完成插入并原子性返回新行数据的唯一可靠方式；它直接返回自增id、默认值、表达式结果等，支持批量插入、on conflict处理及with子句组合，避免竞态与额外查询。

如何在postgresql中用sql批量插入数据并返回新增行？

INSERT ... RETURNING 一次性完成插入和取回

PostgreSQL 的 INSERT ... RETURNING 是唯一可靠、原子性保证的批量插入并返回新增行的方式。它不是“先插再查”，而是在插入执行的同时，把刚写入的行（含自增 ID、默认值、生成列等）直接返回，避免竞态和额外查询开销。

常见错误是试图用 SELECT LASTVAL() 或 currval() 配合批量插入——这只能拿到最后一条的 ID，且在并发场景下极易出错；也有人想用 CTE 拆成两步，但无法保证中间不被其他事务干扰。

RETURNING * 返回整行，适合需要全部字段的场景；若只关心 ID 和时间戳，显式写 RETURNING id, created_at 更清晰、更轻量
批量插入时，VALUES 后可跟多组括号：VALUES (1,'a'), (2,'b'), (3,'c')，每组对应一行返回结果
若表有 GENERATED ALWAYS AS IDENTITY 或 SERIAL 列，RETURNING 能准确拿到分配后的值，包括触发器修改过的字段

带 WITH 子句的复杂批量插入（如关联查询后插入）

当要插入的数据需从其他表计算或过滤而来（比如“把昨天订单中金额 >100 的用户 ID 批量插入到黑名单表，并返回插入的 user_id 和插入时间”），单纯 VALUES 不够用，得用 WITH + INSERT ... SELECT ... RETURNING 组合。

注意：此时 RETURNING 返回的是实际插入的行，不是原始 SELECT 的结果——如果目标表有 ON CONFLICT DO NOTHING，那些被忽略的行不会出现在返回结果里。

WITH data AS (SELECT user_id FROM orders WHERE order_time >= 'yesterday' AND amount > 100) 定义临时数据集
INSERT INTO blacklist (user_id, inserted_at) SELECT user_id, NOW() FROM data ON CONFLICT DO NOTHING RETURNING user_id, inserted_at
不能在 WITH 中直接用 VALUES 然后又在 INSERT 里重复写字段名——容易因顺序错位导致静默写错字段

ON CONFLICT 场景下 RETURNING 的行为差异

使用 ON CONFLICT 时，RETURNING 的内容取决于你选的是 DO NOTHING 还是 DO UPDATE：前者只返回真正插入的新行；后者则无论新插还是更新，都会返回该行最终状态（即更新后的值）。

典型陷阱是以为 DO NOTHING RETURNING * 能拿到所有“尝试插入”的记录——它只返回成功插入的，被冲突忽略的完全不出现，也没有任何提示。

想区分“新增”和“已存在”，必须用 DO UPDATE SET ... RETURNING 并在 SET 中加个标记字段（如 updated_at = NOW(), is_upsert = TRUE），再靠返回值判断
EXCLUDED 伪记录只在 DO UPDATE 分支可用，RETURNING 中可引用它来对比旧值，例如 RETURNING id, EXCLUDED.email AS attempted_email
若目标列有 DEFAULT 或表达式（如 created_at DEFAULT NOW()），RETURNING 拿到的是实际写入值，不是 VALUES 里传的 NULL

客户端接收 RETURNING 结果的实际注意事项

多数 PostgreSQL 驱动（如 psycopg2、pgx、node-postgres）会把 RETURNING 的结果当作普通查询结果集返回，但新手常卡在“为什么 execute() 没返回数据”。关键点在于：必须用能获取结果集的方法（如 query() 或 execute(..., fetch=True)），而非仅执行语句的 execute()。

psycopg2 中，cur.execute("INSERT ... RETURNING id") 后要调 cur.fetchall() 才能拿到结果；直接 cur.execute() 不会自动读取
若批量插入上万行，RETURNING * 可能导致内存暴涨——按需只选必要字段，别无脑写 *
某些 ORM（如 SQLAlchemy）对 RETURNING 支持有限，原生 SQL 模式下才稳定；用 insert().returning() 时注意版本兼容性（returning() 在 1.4+ 才完整支持多行）