PostgreSQL Crosstab查询报错：返回与SQL元组描述不兼容

错误原因分析 #

你遇到的return and sql tuple descriptions are incompatible错误，核心是结果集定义（AS ct部分）完全不符合crosstab的要求：

• 不能将查询语句直接作为列名，必须明确列出每个要透视的item_id作为列名
• rating是integer类型，你却定义成varchar(50)[]，类型不匹配导致结构冲突

基础静态解决方案（适用于少量item_id） #

首先确保安装tablefunc扩展（crosstab函数属于该扩展），然后用正确的列定义编写查询：

-- 安装扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 静态crosstab查询（匹配测试数据）
SELECT *
FROM crosstab(
  -- 主查询：提供用户、项目、评分的有序数据
  $$SELECT user_id, item_id, rating
    FROM tbl
    ORDER BY 1,2$$,
  -- 第二查询：指定透视列的顺序
  $$SELECT DISTINCT item_id FROM tbl ORDER BY 1$$
) AS ct (
  "user_id" varchar(50),
  "item_0" integer,
  "item_1" integer,
  "item_2" integer,
  "item_3" integer
);

执行后会得到你期望的透视结果，注意结果列的顺序必须和第二查询返回的item_id顺序完全一致，列类型要和rating保持一致（integer）。

动态列解决方案（适配2000个item_id的场景） #

当item_id数量多达2000个时，手动写列定义不现实，需要用动态SQL生成列结构：

方式1：直接执行动态查询 #

DO $$
DECLARE
  col_defs text;
BEGIN
  -- 生成所有item_id对应的列定义字符串
  SELECT string_agg(quote_ident(item_id) || ' integer', ', ')
  INTO col_defs
  FROM (SELECT DISTINCT item_id FROM tbl ORDER BY 1) AS items;

  -- 拼接user_id列定义
  col_defs := '"user_id" varchar(50), ' || col_defs;

  -- 动态执行crosstab查询
  EXECUTE format(
    $$
      SELECT *
      FROM crosstab(
        $$SELECT user_id, item_id, rating FROM tbl ORDER BY 1,2$$,
        $$SELECT DISTINCT item_id FROM tbl ORDER BY 1$$
      ) AS ct(%s)
    $$, col_defs
  );
END $$;

方式2：创建函数返回结果 #

如果需要重复调用，可封装成函数：

CREATE OR REPLACE FUNCTION get_pivoted_tbl()
RETURNS SETOF record AS $$
DECLARE
  col_defs text;
BEGIN
  SELECT string_agg(quote_ident(item_id) || ' integer', ', ')
  INTO col_defs
  FROM (SELECT DISTINCT item_id FROM tbl ORDER BY 1) AS items;

  col_defs := '"user_id" varchar(50), ' || col_defs;

  RETURN QUERY EXECUTE format(
    $$
      SELECT *
      FROM crosstab(
        $$SELECT user_id, item_id, rating FROM tbl ORDER BY 1,2$$,
        $$SELECT DISTINCT item_id FROM tbl ORDER BY 1$$
      ) AS ct(%s)
    $$, col_defs
  );
END $$ LANGUAGE plpgsql;

-- 调用函数时需指定列结构（示例匹配测试数据）
SELECT * FROM get_pivoted_tbl() AS (
  "user_id" varchar(50),
  "item_0" integer,
  "item_1" integer,
  "item_2" integer,
  "item_3" integer
);

替代方案（避免超宽表） #

2000列的透视表会非常庞大，部分客户端工具可能无法正常显示，建议用JSON聚合替代：

SELECT user_id, jsonb_object_agg(item_id, rating) AS ratings
FROM tbl
GROUP BY user_id;

该查询会返回每个用户的所有评分作为JSON对象，更适合大量item_id的场景。

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