Fix "COALESCE types text and integer cannot be matched" in PostgreSQL
ERROR: COALESCE types text and integer cannot be matched
COALESCE returns one column, and a column can only have one type. You gave it a text value and a number, so Postgres can't decide what type the result should be. The same rule applies to CASE branches and to the columns of a UNION.
Why it happens
- Using 0 as the fallback for a text column, often out of habit after a LEFT JOIN.
- A number column with a fallback taken from a text column, or the other way round.
- UNION or UNION ALL with the columns in a different order in each SELECT.
How to fix it
1. Use a fallback of the same type
Fails
SELECT u.username, COALESCE(s.plan, 0) AS plan FROM users u LEFT JOIN subscriptions s ON s.user_id = u.id;
Works
SELECT u.username, COALESCE(s.plan, 'none') AS plan FROM users u LEFT JOIN subscriptions s ON s.user_id = u.id;
plan is text, so the fallback must be text too. If you really need to mix, cast the number side: COALESCE(s.plan, s.user_id::text).
2. Line up the UNION columns
Fails
SELECT user_id, event, event_date FROM events UNION ALL SELECT user_id, login_date, 'login' FROM logins;
Works
SELECT user_id, event, event_date FROM events UNION ALL SELECT user_id, 'login', login_date FROM logins ORDER BY user_id, event_date;
UNION matches columns by position, not by name. Here the date landed under event and the text under event_date, so Postgres reports UNION types text and date cannot be matched.
Try it yourself
This is the broken query, running on real PostgreSQL in your browser. Press Run to see the error explained, then fix it.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Practice so it doesn't happen in an interview
- Filling in missing managersNULL · COALESCE
- Total touchpoints per userUNION vs UNION ALL
- Order count, including zeroLEFT JOIN · COUNT(col) vs COUNT(*)
- Wallet balance from a ledgerSUM(CASE) · signed amounts · LEFT JOIN + COALESCE