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

Howund

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. – Dill

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. – Canicular

Answer 1

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

Karalee

This was my issue as well and solving the order fixed the union. – Hysteresis

This is such a simple insight but it's barely documented. You're a lifesaver! – Benzoin

Came here just to say that ORDER was my culprit. I have a large and complex query with multiple CTE's and at one point UNION ALL a few of them, 1 of which was a series of manually/hardcoded values, the others derived values from multiple other CTE's, so it got hard to keep track of order. When developing the hardcoded value CTE, just 2 columns were swapped....2 columns for 2 hours of my life, i'll never get back. CHECK YOUR ORDER! – Does

Answer 2

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.

Dynamite

Answer 3

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.

Marxism