Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

PostgreSQL UNION fails because the column types do not match

ErrorERROR: UNION types text and bigint cannot be matched

Short answer

UNION picks one result type per column from its inputs. If a text column meets a bigint column, or an untyped NULL ends up as text before it meets a number, no implicit conversion exists and the query fails. Cast the offending branch so every branch yields the same type, or put a typed value in the first branch.

  • Applies to PostgreSQL 16, psql 16
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Run each SELECT of the UNION alone and look at the column types. The column that differs is the one in the error.

    \d my_table_a
    \d my_table_b
  2. 2Match what you see, then jump to its fix

    1. 1The error names two real types, for example text and bigintOne branch returns text and another returns a numberFix
    2. 2SELECT NULL UNION SELECT NULL UNION SELECT 1 style queries, or several branches with placeholder NULL columnsA bare NULL or literal in a nested UNION resolves to text too earlyFix
    3. 3The branches select different column lists in a different orderThe same column position holds different types by accidentFix

What does the error look like?

  • ERROR: UNION types text and bigint cannot be matched
  • ERROR: UNION types text and integer cannot be matched

Cause 1 of 3

One branch returns text and another returns a number

1Check

If the non-unknown inputs are not all of the same type category, the resolution fails. Text and numbers are different categories.

\d orders
\d products

2Fix

Cast one side so both branches agree. Usually it is the number that is turned into text.

SELECT CAST(id AS text) AS ref FROM orders
UNION
SELECT code AS ref FROM products;

3Verify

Run the query again; it should return one text column.

SELECT CAST(id AS text) AS ref FROM orders UNION SELECT code FROM products LIMIT 5;

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

A bare NULL or literal in a nested UNION resolves to text too early

1Check

Three or more branches are resolved as nested pairs, left first. Two unknown inputs together resolve to text, and then text meets an integer later in the chain.

SELECT NULL UNION SELECT NULL UNION SELECT 1;

2Fix

Give the leftmost branch a typed value for that column so the unknown ones take its type.

SELECT CAST(NULL AS integer) UNION SELECT NULL UNION SELECT 1;

3Verify

The corrected statement returns one integer column.

SELECT CAST(NULL AS integer) UNION SELECT NULL UNION SELECT 1;

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

The same column position holds different types by accident

1Check

The rules run separately on each output column, by position. A branch with columns in another order puts a text column opposite a number.

SELECT * FROM a LIMIT 0;
SELECT * FROM b LIMIT 0;

2Fix

Name the columns in each branch and keep the same order.

SELECT id, name FROM a
UNION ALL
SELECT id, name FROM b;

3Verify

Run the query; the column types now line up.

SELECT id, name FROM a UNION ALL SELECT id, name FROM b LIMIT 1;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Never use SELECT * inside a UNION; list the columns.
  • Cast placeholder NULL columns in the first branch.
  • Keep id-style columns the same type on every table you will union.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.