How to fix 2200G: most_specific_type_mismatch in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 29, 2025· 168 views

The PostgreSQL "most_specific_type_mismatch" error (2200G) occurs when UNION, CASE, INTERSECT, or EXCEPT queries combine columns or expressions with incompatible data types that cannot be implicitly coerced to a common type. Fixing requires explicitly casting mismatched columns to compatible types.

Quick fix

Fixes 2200G

-- Check column types in a table
\d your_table

-- Problematic example:
SELECT id, name FROM users        -- id: integer, name: text
UNION
SELECT product_id, description FROM products;  -- product_id: text, description: text
  1. Identify the exact failing query
  2. List column types in each branch of the set operation
  3. Cast incompatible columns to a common type
  4. Fix CASE expressions with incompatible branch types
postgresql — error
-- Check column types in a table
\d your_table
-- Problematic example:
SELECT id, name FROM users        -- id: integer, name: text
UNION
SELECT product_id, description FROM products;  -- product_id: text, description: text
2200G: most_specific_type_mismatch

What this error means

The "most_specific_type_mismatch" error (2200G) occurs when PostgreSQL's type resolution system encounters incompatible data types in multi-part queries. This typically happens with UNION, INTERSECT, EXCEPT, or CASE statements where the database cannot find a common type that all branches can be converted to. Unlike some databases that perform automatic type coercion, PostgreSQL requires that all branches of a set operation return compatible types. When you combine multiple SELECT statements with UNION or use CASE expressions with different branch types, PostgreSQL applies strict type checking. If it cannot find a common supertype through implicit casting rules, it raises this error. The type resolution algorithm checks: (1) if all inputs are the same type; (2) if any are domain types; (3) if all are unknown; or (4) if there exists a type all inputs can be implicitly cast to. If none of these apply, you get 2200G.

Typical symptoms

  • UNION queries fail immediately with "2200G: most_specific_type_mismatch"
  • CASE expressions with branches of different types are rejected
  • INTERSECT or EXCEPT queries fail during type checking phase
  • Subquery results cannot be combined due to column type conflicts
  • VALUES clauses with inconsistent types across rows fail to parse
  • GREATEST or LEAST functions with incompatible argument types raise the error
  • Nested UNION queries fail at the outer UNION level

Common causes

  1. UNION query combines columns of different types (e.g., integer in first SELECT, text in second) where no implicit cast exists
  2. CASE expression branches return incompatible types that have no common supertype
  3. INTERSECT or EXCEPT queries attempt to compare tables with differently-typed corresponding columns
  4. Nested UNION queries where intermediate results resolve to a type incompatible with outer query requirements
  5. GREATEST or LEAST functions receive arguments of types with no common parent type
  6. VALUES constructor includes rows with inconsistent column types across different rows
  7. Type casting was removed or changed in migration, breaking existing queries

How to fix "2200G: most_specific_type_mismatch"

1Identify the exact failing query

Enable query logging in PostgreSQL to see the failing SQL statement. Check application error messages and PostgreSQL logs (typically /var/log/postgresql/ on Unix systems, or check the server logs in pgAdmin). Look for the query triggering 2200G and note which operation (UNION, CASE, etc.) is involved.

2List column types in each branch of the set operation

For UNION queries, document the data type of each column in every SELECT branch. Use psql or a GUI tool to inspect column types:

sql
-- Check column types in a table
\d your_table

-- Problematic example:
SELECT id, name FROM users        -- id: integer, name: text
UNION
SELECT product_id, description FROM products;  -- product_id: text, description: text

In this example, id (integer) and product_id (text) are incompatible because integer cannot be implicitly cast to text in a UNION context.

3Cast incompatible columns to a common type

Add explicit type casts using the ::type syntax (PostgreSQL shorthand) or CAST() function:

sql
-- INCORRECT: type mismatch
SELECT id, name FROM users
UNION
SELECT product_id, description FROM products;

-- CORRECT: cast id to text
SELECT id::text, name FROM users
UNION
SELECT product_id, description FROM products;

-- Alternative using CAST()
SELECT CAST(id AS text), name FROM users
UNION
SELECT product_id, description FROM products;

Choose the target type based on: (1) the most general type needed, (2) whether the cast preserves data (e.g., numeric to text is safe; text to numeric may fail).

4Fix CASE expressions with incompatible branch types

Problematic CASE with mixed types:

sql
-- INCORRECT: branches return integer and text
SELECT CASE
  WHEN status = 'active' THEN 1
  ELSE 'inactive'
END;

Fixed by casting all branches to the same type:

sql
-- CORRECT: all branches return text
SELECT CASE
  WHEN status = 'active' THEN '1'::text
  ELSE 'inactive'
END;

-- Or cast to numeric
SELECT CASE
  WHEN status = 'active' THEN 1::numeric
  ELSE 0
END;

Ensure ALL branches (including ELSE) return the same type or compatible types.

5Handle GREATEST and LEAST functions with type mismatches

The GREATEST and LEAST functions require compatible argument types:

sql
-- INCORRECT: mixing integer and text
SELECT GREATEST(user_id, user_name);

-- CORRECT: cast to common type
SELECT GREATEST(user_id::text, user_name);

All arguments must be convertible to a common type.

6Review PostgreSQL implicit casting rules

Understanding PostgreSQL's type coercion rules helps you write better queries. Key implicit casts:

- integer -> numeric, bigint, real, double precision
- text -> varchar (and vice versa)
- smallint -> integer, bigint, numeric

Note: integer to text is NOT an implicit cast; you must use explicit ::text.

Consult the official PostgreSQL documentation: https://www.postgresql.org/docs/current/typeconv.html

7Test the corrected query

Execute the modified query in a development environment:

sql
-- Test your corrected query
EXPLAIN ANALYZE
SELECT id::text, name FROM users
UNION
SELECT product_id, description FROM products;

Verify that the query executes without errors and returns the expected result set. Use EXPLAIN ANALYZE to check performance.

Advanced notes

PostgreSQL's type resolution follows specific rules. For UNION and similar constructs: (1) if all inputs are the same type, use that type; (2) ignore unknown types; (3) if only unknowns remain, default to text; (4) otherwise, find a common supertype that all inputs can implicitly cast to. Domain types are treated as their base types during resolution.

Nested UNIONs can be tricky: the innermost UNION resolves first, and its result type is used in the next outer UNION. Example: SELECT NULL UNION SELECT NULL UNION SELECT 1 fails because the inner SELECT NULL UNION SELECT NULL resolves as text, then text and integer have no common supertype.

Fix nested UNIONs by explicitly casting NULL:

sql
SELECT NULL::int
UNION
SELECT NULL::int
UNION
SELECT 1;

Performance note: casting columns in WHERE clauses can prevent index usage. For example, WHERE id::text = '123' may not use an index on id. Instead, align your schema design or cast the other operand: WHERE id = 123::int. Use EXPLAIN to verify index usage.

Frequently asked questions

What does "2200G" mean?
The "most_specific_type_mismatch" error (2200G) occurs when PostgreSQL's type resolution system encounters incompatible data types in multi-part queries. This typically happens with UNION, INTERSECT, EXCEPT, or CASE statements where the database cannot find a common type that all branches can be converted to. Unlike some databases that perform automatic type coercion, PostgreSQL requires that all branches of a set operation return compatible types.
How do I fix "2200G"?
The PostgreSQL "most_specific_type_mismatch" error (2200G) occurs when UNION, CASE, INTERSECT, or EXCEPT queries combine columns or expressions with incompatible data types that cannot be implicitly coerced to a common type. Fixing requires explicitly casting mismatched columns to compatible types.
What causes "2200G"?
UNION query combines columns of different types (e.g., integer in first SELECT, text in second) where no implicit cast exists CASE expression branches return incompatible types that have no common supertype INTERSECT or EXCEPT queries attempt to compare tables with differently-typed corresponding columns
Is the "2200G" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading