How to fix 2200G: most_specific_type_mismatch in PostgreSQL

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.

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

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

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

-- 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).

Problematic CASE with mixed types:

-- 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:

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

The GREATEST and LEAST functions require compatible argument types:

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

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

Execute the modified query in a development environment:

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