PostgreSQL如何为数据表创建has_($value)格式布尔标识列

固定枚举值实现方案（推荐，无需扩展） #

如果你的value取值固定为10、20、30，直接用CASE语句加聚合即可实现，不需要调用crosstab，逻辑更清晰不容易出错：

SELECT
    id,
    MAX(CASE WHEN value = 10 THEN 1 ELSE 0 END) AS has_10,
    MAX(CASE WHEN value = 20 THEN 1 ELSE 0 END) AS has_20,
    MAX(CASE WHEN value = 30 THEN 1 ELSE 0 END) AS has_30
FROM t
GROUP BY id
ORDER BY id;

执行后返回结果如下：

动态取值实现方案 #

如果value的取值会动态增加，不想每次手动修改SQL，可以用动态SQL自动生成查询语句：

1. 先执行以下语句生成最终的查询SQL：

SELECT 'SELECT id, ' ||
    string_agg(
        'MAX(CASE WHEN value = ' || value || ' THEN 1 ELSE 0 END) AS has_' || value,
        ', '
    ) || ' FROM t GROUP BY id ORDER BY id'
FROM (SELECT DISTINCT value FROM t ORDER BY value) AS distinct_values;

2. 复制上一步返回的SQL执行即可得到对应结果。如果需要完全自动化执行，可以使用PostgreSQL匿名块：

DO $$
DECLARE
    dyn_sql TEXT;
BEGIN
    SELECT 'SELECT id, ' ||
        string_agg(
            'MAX(CASE WHEN value = ' || value || ' THEN 1 ELSE 0 END) AS has_' || value,
            ', '
        ) || ' FROM t GROUP BY id ORDER BY id'
    INTO dyn_sql
    FROM (SELECT DISTINCT value FROM t ORDER BY value) AS distinct_values;
    -- 可通过RAISE NOTICE打印生成的SQL确认正确性
    RAISE NOTICE 'Generated SQL: %', dyn_sql;
    EXECUTE dyn_sql;
END $$;

crosstab方案说明 #

你之前用crosstab没成功大概率是因为没有提前安装tablefunc扩展，或是没有正确定义返回列结构。对于仅需要标记值是否存在的场景，CASE聚合的方案复杂度更低，维护成本更小。

内容的提问来源于stack exchange，提问作者Bruno Henrique L N Peixoto