I am trying to compute a crosstab on a table (happens to be a simple materialized view, but this shouldn't matter):
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)
When I attempt to compute a crosstab on the data with the following expression, I get an error:
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
I'm a at a loss as to why this is happening! Here is the schema of the source table:
user=# \d data
Materialized view "public.data"
Column | Type | Modifiers
--------+--------+-----------
region | text |
date | date |
sum | bigint |
The value columns are of type bigint, not 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)