PostgreSQL中如何按条件复制行并更新列值实现往返行程拆分？

前置说明 #

我们假设你的航班记录表名为flight_records，如果实际表名不同可自行替换。

方案1：仅查询得到预期结果，不修改原表 #

直接通过UNION ALL拼接三类数据即可得到你要的输出：

SELECT customer_id, origin, destination, 1 AS type
FROM flight_records
WHERE type = 1
UNION ALL
-- 取原往返行程的去程，修改type为1
SELECT customer_id, origin, destination, 1 AS type
FROM flight_records
WHERE type = 2
UNION ALL
-- 取原往返行程的返程，互换出发地和目的地，修改type为1
SELECT customer_id, destination AS origin, origin AS destination, 1 AS type
FROM flight_records
WHERE type = 2
ORDER BY customer_id;

方案2：直接修改原表（插入返程记录+更新原有记录） #

如果需要改动原表数据，可按以下两步操作，建议放在事务中执行保证数据一致性：

BEGIN;
-- 第一步：插入互换出发/到达地的返程记录，type直接设为1
INSERT INTO flight_records (customer_id, origin, destination, type)
SELECT customer_id, destination, origin, 1
FROM flight_records
WHERE type = 2;

-- 第二步：把原有type=2的记录的type修改为1
UPDATE flight_records
SET type = 1
WHERE type = 2;
COMMIT;

注：如果你的表设有自增主键等唯一约束，插入操作会自动生成新的主键值，无需额外处理；如果有自定义联合唯一约束，请根据实际规则调整逻辑。

内容的提问来源于stack exchange，提问作者sam.marhaendra