PostgreSQL UNION fails because the column types do not match
ERROR: UNION types text and bigint cannot be matchedShort 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.
Fix it fast
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_b2Match what you see, then jump to its fix
- 1
The error names two real types, for example text and bigintOne branch returns text and another returns a numberFix - 2
SELECT 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
The branches select different column lists in a different orderThe same column position holds different types by accidentFix
- 1
What does the error look like?
ERROR: UNION types text and bigint cannot be matchedERROR: 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 products2Fix
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
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
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
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
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix