如何在PostgreSQL中按原表顺序合并两个同结构表？

问题说明 #

使用SELECT * FROM sample1 UNION SELECT * FROM sample2合并表时，结果顺序是不确定的——因为SQL标准中，除非显式指定ORDER BY，否则查询结果的顺序不做保证。要实现先显示sample1的原有插入顺序数据，再显示sample2的原有插入顺序数据，需要通过添加辅助排序字段来实现。

解决方案1：临时添加排序字段（无需修改表结构） #

如果表数据没有经过更新或删除操作，可通过生成临时行号来保留插入顺序，同时用标识字段区分两个表的优先级：

SELECT ticker, pe, grow, peg
FROM (
    -- 给sample1标记优先级1，并生成行号保留插入顺序
    SELECT *, 
           1 AS table_priority,
           ROW_NUMBER() OVER () AS row_seq
    FROM sample1
    UNION ALL
    -- 给sample2标记优先级2，生成行号保留插入顺序
    SELECT *, 
           2 AS table_priority,
           ROW_NUMBER() OVER () AS row_seq
    FROM sample2
) AS merged_data
-- 先按表优先级排序，再按每个表内部的行号排序
ORDER BY table_priority, row_seq;

注意：这里用UNION ALL替代UNION，因为两个表的ticker没有重复值，UNION会额外执行去重和排序操作，既影响性能又破坏原有顺序。

解决方案2：添加自增主键（更可靠的长期方案） #

如果表数据可能会被更新或删除，临时行号的方式可能无法保证顺序，建议给表添加自增主键字段，用主键来标记插入顺序：

1. 给两个表添加自增主键：

ALTER TABLE sample1 ADD COLUMN id SERIAL PRIMARY KEY;
ALTER TABLE sample2 ADD COLUMN id SERIAL PRIMARY KEY;

2. 合并查询并按优先级和主键排序：

SELECT ticker, pe, grow, peg
FROM (
    SELECT *, 1 AS table_priority FROM sample1
    UNION ALL
    SELECT *, 2 AS table_priority FROM sample2
) AS merged_data
ORDER BY table_priority, id;

自增主键id在插入数据时自动生成，不会因数据的物理存储变化而改变，能稳定保留插入顺序。

内容的提问来源于stack exchange，提问作者showkey