PostgreSQL Most Specific Type Mismatch (SQLSTATE 2200G)

When PostgreSQL raises ERROR: most specific type mismatch with SQLSTATE 2200G, it signals a most_specific_type_mismatch condition. This error belongs to the SQL standard's data exception class (class 22) and is triggered when the most-specific type of a value — typically derived from a typed XML document or a structured type — is not compatible with the declared type expected in that context.

What This Error Means

SQLSTATE 2200G is part of the SQL data exception class 22, a broad category covering violations related to data values and their types. The specific condition most_specific_type_mismatch originates from the SQL/XML standard (ISO/IEC 9075-14), which defines typed XML and schema-aware XML processing.

In SQL/XML semantics, the "most-specific type" refers to the actual runtime type of a typed value — for example, the XML schema type that a validated XML instance conforms to. When you pass a value whose most-specific type is incompatible with the declared type of a target column, parameter, or expression, PostgreSQL raises this error to enforce type safety at the SQL level.

In practice, this error surfaces most commonly in contexts involving XMLQUERY, XMLTABLE, XMLCAST, or other XML-related SQL/XML functions where the result or argument type has been constrained by an XML schema or a declared SQL type. PostgreSQL's handling of this class of error is consistent with the SQL:2003 and later SQL standards. After this error is raised, the current transaction is in an aborted state and must be rolled back before issuing further commands.

Common Causes

1. Passing a typed XML value to a column or parameter expecting a different XML schema type. When using SQL/XML features with XML schema validation, a value that has been validated against one schema type cannot be silently coerced into a position expecting a different, incompatible schema type.

2. XMLCAST failing due to incompatible type derivation. When casting a typed XML value using XMLCAST, if the source value's most-specific type is not in the type hierarchy of the target type (neither a subtype nor a supertype), the cast is not permitted and this error is raised.

3. XMLQUERY or XMLTABLE returning a typed node with an unexpected concrete type. An XQuery expression evaluated via XMLQUERY or XMLTABLE may return a typed atomic value whose schema type is incompatible with the column definition in the COLUMNS clause.

4. Stored procedure or function parameter type mismatch with SQL/XML structured types. Invoking a function that declares a parameter of a specific XML schema type with a value whose most-specific type is a different, incompatible schema type.

How to Fix most_specific_type_mismatch

1. Verify the XML schema type of the value you are passing. Inspect the actual type of the XML value at the point where the error occurs. If you have control over the schema, ensure the value's type aligns with the expected type in the target context.

2. Use explicit casting where the type hierarchy allows it. If the types are related (subtype/supertype), an explicit XMLCAST can resolve the mismatch:

   SELECT XMLCAST(your_xml_value AS XML(SEQUENCE)) FROM your_table;
   

3. Untype the XML value before passing it. If strict schema typing is not required in the target context, strip the type annotation by casting to untyped XML:

   SELECT XMLCAST(your_xml_value AS XML) FROM your_table;
   

4. Adjust XMLTABLE column definitions to match actual node types. If you are using XMLTABLE and a column type is too restrictive, widen or correct the declared type in the COLUMNS clause:

   SELECT *
   FROM XMLTABLE(
     '/root/item'
     PASSING your_xml_doc
     COLUMNS
       id   INTEGER PATH '@id',
       name TEXT    PATH 'name'  -- ensure the PATH result type matches TEXT
   );
   

5. Roll back the transaction and re-examine the data pipeline. Because this error aborts the current transaction, always issue a ROLLBACK before retrying:

   ROLLBACK;
   -- Investigate and fix the type mismatch, then retry
   

Additional Information

• SQLSTATE 2200G is defined in the SQL standard (SQL:2003 and later, specifically the SQL/XML part). It is a relatively uncommon error in typical application code and appears almost exclusively in SQL/XML-heavy workloads.
• Related SQLSTATE codes in the data exception class 22 include:
  • 2200M — invalid_xml_document
  • 2200N — invalid_xml_content
  • 2200S — invalid_xml_comment
  • 2200T — invalid_xml_processing_instruction
  • 22000 — data_exception (generic)
• PostgreSQL's SQL/XML support has evolved over multiple versions. Richer typed XML support was added progressively; if you are running a version older than PostgreSQL 9.4, some SQL/XML behaviors may differ.
• Most ORM frameworks and database drivers do not use SQL/XML typed features directly, so this error is rarely seen through ORMs. It is far more likely to appear in direct SQL or in PL/pgSQL functions that manipulate XML with schema awareness.
• There are no performance implications specific to this error beyond the overhead of rolling back the aborted transaction.

Frequently Asked Questions

Why do I get 2200G only in some rows and not others? The most-specific type of an XML value is determined at the time the value was created or validated. If some rows contain XML that was validated against one schema type and other rows contain XML of a compatible or untyped form, only the rows with the incompatible typed values will trigger the error. Check whether the XML data in the failing rows was produced by a different code path or schema validator.

Is this error related to regular SQL column type mismatches? No. Ordinary type mismatches between SQL scalar types (e.g., passing a string where an integer is expected) raise different errors such as 42804 (datatype_mismatch) or 22P02 (invalid_text_representation). SQLSTATE 2200G is specific to SQL/XML typed value semantics and the concept of "most-specific type" as defined in the SQL/XML standard.

Can I suppress or ignore this error? You cannot silently suppress it — it aborts the current transaction. The correct approach is to fix the type mismatch at the source, either by adjusting the XML value's type, casting it to an acceptable type, or modifying the target column or parameter declaration.

Does this error mean my database or data is corrupted? No. This is a type-safety enforcement error, not a sign of data corruption. It means there is a type contract violation in your SQL/XML code or data pipeline. Resolving the type mismatch will clear the error without any need to repair data.