PostgreSQL return and sql tuple descriptions are incompatible

Question

What's wrong with this query? I've been bashing my head with the data type but didn't see any problem.

Thanks.

select * from crosstab(
$$
select * from 
(values
(1546300800, 187923, 1.5),
(1546300800, 187924, 200),
(1546300800, 187926, 120),
(1546300800, 187927, 100),
(1546387200, 187923, 1.5),
(1546387200, 187924, 250),
(1546387200, 187926, 120),
(1546387200, 187927, 125),
(1546473600, 187923, 1.5),
(1546473600, 187924, 275),
(1546473600, 187926, 120),
(1546473600, 187927, 137.5),
(1546560000, 187923, 1.75)
) as t (datetime, trace, value)
$$
)
as final_result (
        unixdatetime int, 
        trace1 double precision, 
        trace2 double precision, 
        trace3 double precision, 
        trace4 double precision
        )

Answer 1

Although you didn't mention which error you are getting, this is how to make it work:

Initialize the module tablefunc:

CREATE EXTENSION tablefunc;

Then, you need to be careful about the data types. PostgreSQL can be quite picky and doesn't auto-magically convert them in many cases. In this case, they must match or at least the crosstab column type definitions must be able to completely contain the types as returned by the crosstab function SQL.

You have 2 options here:

1. Adapt the outer type to a variable numeric type, e.g. decimal:

select * from crosstab(
$$
select * from (values
    (1546300800, 187923, 1.5),
    (1546300800, 187924, 200),
    (1546300800, 187926, 120),
    (1546300800, 187927, 100),
    (1546387200, 187923, 1.5),
    (1546387200, 187924, 250),
    (1546387200, 187926, 120),
    (1546387200, 187927, 125),
    (1546473600, 187923, 1.5),
    (1546473600, 187924, 275),
    (1546473600, 187926, 120),
    (1546473600, 187927, 137.5),
    (1546560000, 187923, 1.75)
) as t (datetime, trace, value)
$$
) as final_result (
    unixdatetime int, 
    trace1 decimal,
    trace2 decimal,
    trace3 decimal,
    trace4 decimal
);

2. Alternatively, make sure the crosstab SQL returns the desired type explicitly, e.g.:

select * from crosstab(
$$
select * from (values
    (1546300800, 187923, 1.5::double precision),
    (1546300800, 187924, 200::double precision),
    (1546300800, 187926, 120::double precision),
    (1546300800, 187927, 100::double precision),
    (1546387200, 187923, 1.5::double precision),
    (1546387200, 187924, 250::double precision),
    (1546387200, 187926, 120::double precision),
    (1546387200, 187927, 125::double precision),
    (1546473600, 187923, 1.5::double precision),
    (1546473600, 187924, 275::double precision),
    (1546473600, 187926, 120::double precision),
    (1546473600, 187927, 137.5::double precision),
    (1546560000, 187923, 1.75::double precision)
) as t (datetime, trace, value)
$$
) as final_result (
    unixdatetime int, 
    trace1 double precision,
    trace2 double precision,
    trace3 double precision,
    trace4 double precision
);

The result in both cases looks as follows:

 unixdatetime | trace1 | trace2 | trace3 | trace4 
--------------+--------+--------+--------+--------
   1546300800 |    1.5 |    200 |    120 |    100
   1546387200 |    1.5 |    250 |    120 |    125
   1546473600 |    1.5 |    275 |    120 |  137.5
   1546560000 |   1.75 |        |        |       
(4 rows)