PostgreSQL如何转置表生成目标视图或表，crosstab执行报错如何解决

前置说明 #

crosstab报错通常是依赖缺失、语法不规范导致的，以下提供两种可直接落地的实现方案，可按需生成实体表或视图。

方案1：crosstab函数实现（适合列数较多的场景） #

crosstab属于tablefunc扩展内置函数，使用前需要先安装扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

完整语法示例（以常见的3列源表结构<行标识列、属性列、值列>为例）： #

-- 生成实体表，需要视图的话把CREATE TABLE 表B AS替换为CREATE VIEW 视图B AS即可
CREATE TABLE 表B AS
SELECT * FROM crosstab(
  -- 第一个参数：源数据查询，必须先按行标识列排序，再按属性列排序，否则会出现数据错位
  'SELECT 行标识列, 属性列, 值列 FROM 表A ORDER BY 1,2',
  -- 第二个参数：获取所有属性列的唯一值，避免属性缺失导致列错位
  'SELECT DISTINCT 属性列 FROM 表A ORDER BY 1'
) AS result(
  -- 此处定义返回结果的列结构，第一个为行标识列，后续依次对应所有属性列，数据类型必须和源表值列的类型匹配
  行标识列 类型,
  属性1 类型,
  属性2 类型,
  属性N 类型
);

常见报错排查点： #

• 未安装tablefunc扩展导致函数不存在
• 返回列的数量、类型和实际转置结果不匹配
• 源查询未按「行标识列→属性列」的顺序排序

方案2：条件聚合实现（适合列数较少、兼容性要求高的场景） #

不需要依赖额外扩展，语法更简单不易出错：

CREATE TABLE 表B AS
SELECT
  行标识列,
  MAX(CASE WHEN 属性列 = '属性1' THEN 值列 END) AS 属性1,
  MAX(CASE WHEN 属性列 = '属性2' THEN 值列 END) AS 属性2,
  -- 按需添加更多属性列
FROM 表A
GROUP BY 行标识列;

需要生成视图的话同样把CREATE TABLE 表B AS替换为CREATE VIEW 视图B AS即可。

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