Styjun

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
 )

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)

Thank you guys for the help! Ancoron's solution worked perfectly.

Regarding the 2nd solution, instead of explicitly casting each value in each row into double precision, we can apply the type cast to the column name as in the code below:

select * from crosstab(
$$
select datetime, trace, value::double precision 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
 )