如何使用Union将PostgreSQL宽表转换为长表？

你需要在每个UNION ALL分支里补充目标表要求的category、Date、Type字段，同时关联原表的visit_date和对应检查项的状态值。以下是完整的SQL示例，你可以根据实际业务含义调整category和Type的固定值：

SELECT
    '排放口' AS category,
    "visit_date" AS Date,
    '常规检查' AS Type,
    "outfalls_free" AS Status
FROM public."Weekly Inspections"
UNION ALL
SELECT
    'Lagoon衬层' AS category,
    "visit_date" AS Date,
    '常规检查' AS Type,
    "lagoon_liner" AS Status
FROM public."Weekly Inspections";

关键说明： #

• UNION ALL vs UNION：用UNION ALL是因为它会保留所有原始记录（包括重复项），比UNION（自动去重）更高效，完全适配宽表转长表的场景。
• 固定字段赋值：category和Type是根据每个检查项的业务意义手动设置的，比如outfalls_free对应"排放口"分类，你需要根据实际业务调整这些字符串内容。
• 字段对齐要求：每个SELECT分支的字段数量、顺序、数据类型必须完全一致，否则UNION ALL会直接报错。
• 扩展更多检查项：如果原表还有其他类似outfalls_free的检查字段（比如pump_status），只需新增一个UNION ALL分支，重复上述结构即可。

如果需要将结果直接保存为新表，可以在SQL开头加上CREATE TABLE 目标表名 AS，示例如下：

CREATE TABLE public."Inspection_Long_Form" AS
SELECT
    '排放口' AS category,
    "visit_date" AS Date,
    '常规检查' AS Type,
    "outfalls_free" AS Status
FROM public."Weekly Inspections"
UNION ALL
SELECT
    'Lagoon衬层' AS category,
    "visit_date" AS Date,
    '常规检查' AS Type,
    "lagoon_liner" AS Status
FROM public."Weekly Inspections";

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