postgresql UNION 类型不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31520629/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UNION type mismatch
提问by user4287146
When I run below query I am getting this error
当我在查询下方运行时，出现此错误
UNION types text and bigint cannot be matched
UNION 类型 text 和 bigint 无法匹配
SELECT
1 AS STEP
, '' AS ProviderName
, '' AS Procedurecode
, Claimid
, Patient_First_Name
, Patient_Last_Name
, DOS
, SUM(COALESCE(Total_Charge,0))
, SUM(COALESCE(PaidAmount,0))
, PostedDate
, CheckEFTDate
, CheckEFTNo
FROM table_name
GROUP BY ProviderName, Claimid, Patient_First_Name, Patient_Last_Name, DOS, PostedDate,
CheckEFTDate, CheckEFTNo
UNION ALL
SELECT
2 AS STEP
, '' AS ProviderName
, '' AS Procedurecode
, COUNT(Claimid)
, '' AS Patient_First_Name
, '' AS Patient_Last_Name
, NULL::date AS DOS
, SUM(COALESCE(Total_Charge,0))
, SUM(COALESCE(PaidAmount,0))
, NULL::date AS PostedDate
, NULL::date AS CheckEFTDate
, '' AS CheckEFTNo
FROM table_name
GROUP BY Claimid
回答by srghma
My mistake was is that in union name of columns doesnt matter, but order does matter(maybe I'm wrong, I can't find documentation)
我的错误是列的联合名称无关紧要，但顺序很重要（也许我错了，我找不到文档）
Example:
例子：
1) This is fine
1）这很好
select
1 :: integer as someint,
'1' :: text as sometext
union
select
2 :: integer as someint,
'2' :: text as sometext
returns
回报
someint sometext
1 1 1
2 2 2
2) this is not fine
2）这不好
select
1 :: integer as someint,
'1' :: text as sometext
union
select
'2' :: text as sometext,
2 :: integer as someint
throws
投掷
Error(s), warning(s):
42804: UNION types integer and text cannot be matched
try yourself https://rextester.com/l/postgresql_online_compiler
回答by Patrick
Most likely - although impossible to know for sure because you haven't posted the table definition - the field claimidis of type text(or varchar, but that is all the same) while count(claimid)produces a bigint. In that case, a quick fix would be to do count(claimid)::text.
最有可能的 - 虽然不可能确切知道，因为您没有发布表定义 - 该字段claimid的类型text（或varchar，但都是一样的）而count(claimid)产生bigint. 在这种情况下，快速解决方法是执行count(claimid)::text.
Otherwise it is rather unclear what you want to achieve. In the top select you obviously want to sum charges and paid amounts per patients. And the bottom select is supposed to sum charges and paid amounts for all patients combined? You should not try to combine such different things in a single query. Better have two different queries that have an obvious function and which do not depend on knowledge of qualifiers like step.
否则就不清楚你想要实现什么。在最上面的选择中，您显然希望对每位患者的费用和支付金额进行汇总。底部选择应该汇总所有患者的费用和支付金额吗？您不应该尝试在单个查询中组合这些不同的内容。最好有两个不同的查询，它们具有明显的功能并且不依赖于像step.
