DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 2200G Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 2200G: Most Specific Type Mismatch

PostgreSQL error code 2200G (most_specific_type_mismatch) is a SQL-standard data exception that occurs when a value's type does not match the most specific (most derived) type expected in a context involving type hierarchies, XML schema types, or user-defined structured types. It most commonly appears when working with composite types, domain hierarchies, or XML processing functions where type inheritance or derivation is in play. While relatively rare in everyday CRUD operations, it can be a significant pain point in enterprise applications with complex type systems.

Top 3 Causes and Fixes

1. Composite or Domain Type Hierarchy Mismatch

When a function expects a specific domain or composite type but receives a parent/base type, PostgreSQL raises 2200G. Always cast explicitly to the most specific required type.

-- Define types
CREATE TYPE base_info AS (name TEXT, value INTEGER);
CREATE DOMAIN specific_info AS base_info;

-- Function expecting the specific domain type
CREATE OR REPLACE FUNCTION handle_info(data specific_info)
RETURNS TEXT AS $$
BEGIN
    RETURN (data).name || ': ' || (data).value;
END;
$$ LANGUAGE plpgsql;

-- WRONG: passing base type causes mismatch
-- SELECT handle_info(ROW('test', 42)::base_info);

-- CORRECT: explicit cast to the most specific type
SELECT handle_info(ROW('test', 42)::specific_info);
Enter fullscreen mode Exit fullscreen mode

2. XML Type Processing Mismatch

Using XML functions like XMLTABLE or XMLCAST without explicitly matching the expected schema type can trigger this error. Always declare column types explicitly.

-- Correct: explicitly typed columns in XMLTABLE
SELECT *
FROM XMLTABLE(
    '//product'
    PASSING XMLPARSE(DOCUMENT '
        <products>
            <product>
                <id>1</id>
                <price>29.99</price>
            </product>
        </products>
    ')
    COLUMNS
        product_id  INTEGER  PATH 'id',
        price       NUMERIC  PATH 'price'
);

-- Explicit XMLCAST to resolve type ambiguity
SELECT XMLCAST(
    XMLQUERY('//price/text()'
        PASSING XMLPARSE(DOCUMENT '<data><price>29.99</price></data>')
        RETURNING CONTENT
    ) AS NUMERIC
);
Enter fullscreen mode Exit fullscreen mode

3. Function Overload Resolution Failure

When multiple overloaded functions exist and PostgreSQL cannot determine the most specific type match, this error (or a related one) is raised. Use explicit casting to force the correct overload.

-- Two overloaded versions
CREATE OR REPLACE FUNCTION process(val INTEGER)
RETURNS TEXT AS $$ BEGIN RETURN 'int:' || val; END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION process(val NUMERIC)
RETURNS TEXT AS $$ BEGIN RETURN 'numeric:' || val; END; $$ LANGUAGE plpgsql;

-- Ambiguous call
-- SELECT process(100);   -- may cause issues

-- Explicit cast to select the correct overload
SELECT process(100::INTEGER);
SELECT process(100::NUMERIC);

-- Inspect available overloads for debugging
SELECT proname, pg_get_function_identity_arguments(oid) AS signature
FROM pg_proc
WHERE proname = 'process'
  AND pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  • Always use explicit casting (::type or CAST(val AS type)) when working with domain types or overloaded functions.
  • Verify type hierarchies using pg_type and pg_cast system catalogs before deploying complex type-dependent code.
  • Use pg_typeof() during debugging to confirm the actual runtime type of any expression. 
-- Helpful debugging queries
SELECT pg_typeof(ROW('test', 42)::specific_info);
SELECT castsource::regtype, casttarget::regtype, castcontext
FROM pg_cast
WHERE castsource = 'base_info'::regtype;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Enforce explicit typing in code reviews: Adopt a team convention requiring explicit casts in all SQL involving custom or domain types. Integrate a SQL linter (e.g., sqlfluff) into your CI pipeline to catch implicit type reliance early.

  2. Audit your type catalog regularly: Run periodic checks against pg_type to map out your type hierarchies. Undocumented or forgotten domain chains are a common root cause of 2200G surfacing unexpectedly after schema changes or PostgreSQL version upgrades.

Related Errors

Code Name Notes
22000 data_exception Parent category of 2200G
42804 datatype_mismatch Similar but occurs at DDL/DML level
42883 undefined_function Often co-occurs with overload resolution failures
2200M invalid_xml_document Related XML processing error

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)