postgresql - 如何在 PostgreSQL 中成功 UNION ALL 以获得双精度？

Question

我遇到了一个非常奇怪的问题。

此查询不起作用：

SELECT
       t1.status :: TEXT,
       t1.total_amount :: float AS amount

FROM accounting.payment_in_records AS t1
UNION ALL
SELECT
       t1.fees :: float          AS amount,
       'Processed' :: TEXT       AS status
FROM accounting.payment_out_record_detail_groups AS t1
WHERE t1.fees > 0;

错误：

[42804] ERROR: UNION types text and double precision cannot be matched Position: 147

但是这个查询有效：

SELECT
       t1.total_amount :: float AS amount,
       t1.status :: TEXT
FROM accounting.payment_in_records AS t1
UNION ALL
SELECT
       t1.fees :: float          AS amount,
       'Processed' :: TEXT       AS status
FROM accounting.payment_out_record_detail_groups AS t1
WHERE t1.fees > 0;

所以列顺序必须相同？

标签： postgresqlunion

Answer 1

是的，顺序必须相同。决定因素不是列别名，而是列位置。

有关“兼容数据类型”的详细信息，请参阅文档。UNION

文档有

解析算法分别应用于联合查询的每个输出列。

它没有明确说明一列是由它的位置来标识的。