PostgreSQL动态crosstab查询生成临时表无数值问题排查

问题背景 #

你有如下事实表，静态方式使用crosstab函数生成交叉表可得到正确结果，但动态生成时所有城市列的数值为空：

CREATE TABLE fact_table (
  customer VARCHAR(50),
  product VARCHAR(50),
  city VARCHAR(50),
  measure INTEGER
);

INSERT INTO fact_table (customer, product, city, measure)
VALUES
  ('Oleg', 'Shampoo', 'Zurich', 5),
  ('Oleg', 'Bread', 'Bern', 1),
  ('Murad', 'Shampoo', 'Bern', 3),
  ('Murad', 'Beer', 'Lausanne', 4),
  ('Olva', 'Beer', 'Lausanne', 2),
  ('Olva', 'Bread', 'Zurich', 2),
  ('Olva', 'Shampoo', 'Bern', 1);

CREATE EXTENSION IF NOT EXISTS tablefunc;

静态正确写法：

CREATE TABLE temp_t AS 
SELECT * 
FROM 
crosstab(
' SELECT customer, city, sum(measure) FROM fact_table 
           GROUP BY customer, city ORDER BY 1,2'
, 
$values$ VALUES ('Bern'), ('Lausanne'), ('Zurich')$values$
) 
AS ct(customer text, bern integer, lausanne integer, zurich integer)
;

数值为空的核心原因 #

动态生成时出现空值，主要是以下几个匹配逻辑错误导致：

• 城市列表顺序与主查询排序不匹配
  crosstab函数要求第二个参数返回的城市顺序，必须和主查询中ORDER BY的城市顺序完全一致。如果动态生成城市列表时没有按ORDER BY city排序，会导致数值无法映射到正确的列，最终显示为空。

• 列定义与城市列表顺序不对应
  动态生成的结果列定义（比如bern integer, lausanne integer）必须和第二个参数返回的城市列表顺序严格一一对应。如果顺序错位，就会出现列值为空或错位的情况。

• 大小写/拼写不一致
  PostgreSQL默认区分大小写，若动态生成的城市名称（比如小写'bern'）和主查询中的城市值（大写'Bern'）拼写、大小写不匹配，crosstab无法匹配到对应数据，导致列空。

正确的动态实现示例 #

使用PL/pgSQL编写动态SQL，严格保证顺序和匹配一致性：

DO $$
DECLARE
    city_list text;
    col_defs text;
BEGIN
    -- 按主查询的排序规则生成城市列表
    SELECT string_agg(format('(%L)', city), ', ') INTO city_list
    FROM (SELECT DISTINCT city FROM fact_table ORDER BY city) t;

    -- 生成和城市列表顺序完全对应的列定义
    SELECT string_agg(lower(city) || ' integer', ', ') INTO col_defs
    FROM (SELECT DISTINCT city FROM fact_table ORDER BY city) t;

    -- 动态执行创建临时表的SQL
    EXECUTE format('
        CREATE TEMP TABLE temp_t AS
        SELECT *
        FROM crosstab(
            ''SELECT customer, city, sum(measure) FROM fact_table GROUP BY customer, city ORDER BY 1,2'',
            ''VALUES %s''
        ) AS ct(customer text, %s)
    ', city_list, col_defs);
END $$;

执行后查询temp_t即可得到正确的交叉表结果。

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