UNION types "..." and "..." cannot be matched

Question

When I run below query I am getting this error

UNION types text and bigint cannot be matched

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

presumably it's the last field: `CheckEFTNo` and `''`. you can't mix types of fields in a union query. whatever the type of the field is in the FIRST query of the union, all other queries have to output the SAME field type. — Marc B, Jul 20 '15 at 15:37
Check the column data types in both select statements. May be you are getting different data types in one of the columns. You can Type cast columns values using "::" operator to make them same type. — Abhishek Ginani, Jul 20 '15 at 17:25

score 26 · Answer 1 · answered Jan 18 '19 at 11:46

26

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

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

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

answered Jan 18 '19 at 11:46

srghma

4,770
2
38
54

2

This was my issue as well and solving the order fixed the union. – Suliman Sharif Jan 14 '20 at 20:04
3

This is such a simple insight but it's barely documented. You're a lifesaver! – btown Jan 21 '21 at 14:04

score 2 · Answer 2 · answered Jan 22 '23 at 17:25

With UNION, I got the similar error below:

ERROR: UNION types character varying and integer cannot be matched
LINE 1: SELECT name, age FROM student UNION SELECT age, name FROM te...

Because the types of columns are not the same order as shown below:

 -- VARCHAR  INTEGER
 --     ↓     ↓            
SELECT name, age FROM student 
UNION 
SELECT age, name FROM teacher;
 --     ↑     ↑
 -- INTEGER  VARCHAR

So, I changed the order as shown below then the error was solved:

 -- VARCHAR  INTEGER
 --     ↓     ↓            
SELECT name, age FROM student 
UNION 
SELECT name, age FROM teacher;
 --     ↑     ↑
 -- VARCHAR  INTEGER

In addition, I renamed name column to first_name column as shown below:

ALTER TABLE teacher RENAME COLUMN name to first_name;

Then, the query below also works even though the 1st column names are different because the types of columns are the same order as shown below:

 -- VARCHAR  INTEGER
 --     ↓     ↓            
SELECT name, age FROM student 
UNION 
SELECT first_name, age FROM teacher;
 --        ↑        ↑
 --    VARCHAR   INTEGER

Lastly, the documentation says below:

The two SELECT statements that represent the direct operands of the UNION must produce the same number of columns, and corresponding columns must be of compatible data types.

score 1 · Answer 3 · answered Jul 21 '15 at 09:44

Most likely - although impossible to know for sure because you haven't posted the table definition - the field claimid is 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.

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.

UNION types "..." and "..." cannot be matched

3 Answers3

Linked