postgresql 交叉表问题(“返回和sql元组描述不兼容”)

laik7k3q 于 2022-12-23 发布在 PostgreSQL

关注(0)|答案(1)|浏览(165)

我正在尝试计算一个表的交叉表（碰巧是一个简单的物化视图，但这并不重要）：

user=# select * from data;
 region |    date    | sum
--------+------------+-----
 East   | 2010-06-30 |  22
 East   | 2010-01-31 |  32
 East   | 2010-02-25 |  12
 North  | 2010-01-31 |  34
 North  | 2010-02-25 |  88
 South  | 2010-01-31 |  52
 South  | 2010-02-25 |  54
 South  | 2010-06-30 |  11
 West   | 2010-06-30 |  15
 West   | 2010-02-25 |  37
 West   | 2010-01-31 |  11
(11 rows)

当我试图用下面的表达式计算数据的交叉表时，我得到了一个错误：

user=# SELECT * FROM
       crosstab('select region, date, sum from x order by 1') 
       AS ct (region text, d1 date, d2 date, d3 date);
ERROR:  return and sql tuple descriptions are incompatible

我不知道为什么会发生这种情况！下面是源表的模式：

user=# \d data
 Materialized view "public.data"
 Column |  Type  | Modifiers 
 --------+--------+-----------
 region | text   | 
 date   | date   | 
 sum    | bigint |

postgresql

来源：https://stackoverflow.com/questions/34030761/crosstab-troubles-return-and-sql-tuple-descriptions-are-incompatible

1条答案

按热度按时间

nhaq1z211#

value列的类型为bigint，而不是date：

SELECT * 
FROM crosstab(
    'select region, date, sum from data order by 1'
) 
AS result (region text, d1 bigint, d2 bigint, d3 bigint);

 region | d1 | d2 | d3 
--------+----+----+----
 East   | 22 | 32 | 12
 North  | 34 | 88 |   
 South  | 52 | 54 | 11
 West   | 15 | 37 | 11
(4 rows)

赞(0）回复(0）举报 2022-12-23

我来回答

postgresql 交叉表问题(“返回和sql元组描述不兼容”)

1条答案

相关问题

热门标签

最新问答