PostgreSQL 返回和 sql 元组描述不兼容

Question

这个查询有什么问题？我一直在用数据类型抨击我的头脑，但没有发现任何问题。

谢谢。

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

虽然你没有提到你得到的是哪个错误，但这是让它工作的方法:

初始化模块tablefunc :

CREATE EXTENSION tablefunc;

然后，您需要注意数据类型。 PostgreSQL 可能非常挑剔，在许多情况下不会自动神奇地转换它们。在这种情况下，它们必须匹配，或者至少交叉表列类型定义必须能够完全包含交叉表函数 SQL 返回的类型。

这里有 2 个选项:

1. 将外部类型调整为可变数字类型，例如十进制:

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
);

1. 或者，确保交叉表 SQL 显式返回所需的类型，例如:

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
);

两种情况下的结果如下所示:

 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)