3

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 |
abhillman
  • 3,942
  • 1
  • 20
  • 21

1 Answers1

6

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)
klin
  • 112,967
  • 15
  • 204
  • 232