DEV Community: umzzil nng

PostgreSQL 22031 Error: Causes and Solutions Complete Guide

umzzil nng — Tue, 16 Jun 2026 14:02:53 +0000

PostgreSQL Error 22031: Invalid Argument for SQL JSON Datetime Function

PostgreSQL error code 22031 occurs when the datetime() function inside a SQL/JSON path expression receives an argument it cannot parse into a valid date or time value. This typically happens when JSON string fields contain date values in unexpected formats or when a mismatched template string is supplied. Understanding how PostgreSQL's JSON path datetime() function works is key to resolving and preventing this error.

Top 3 Causes

1. Non-ISO 8601 Date Strings in JSON Data

PostgreSQL's datetime() in JSON path expressions expects ISO 8601 format by default (YYYY-MM-DD, YYYY-MM-DDTHH:MI:SS, etc.). Any deviation triggers error 22031.

-- This FAILS: slash-separated date format
SELECT jsonb_path_query(
  '{"ts": "08/15/2024"}',
  '$.ts.datetime()'
);
-- ERROR: invalid argument for sql json datetime function

-- This WORKS: ISO 8601 format
SELECT jsonb_path_query(
  '{"ts": "2024-08-15"}',
  '$.ts.datetime()'
);

2. Mismatched Template Format String (PostgreSQL 15+)

PostgreSQL 15 introduced template support for datetime(). If the template doesn't match the actual data structure, 22031 is raised.

-- FAILS: wrong template for the actual data
SELECT jsonb_path_query(
  '{"ts": "15-08-2024"}',
  '$.ts.datetime("YYYY-MM-DD")'
);
-- ERROR: 22031

-- WORKS: template matches data layout
SELECT jsonb_path_query(
  '{"ts": "15-08-2024"}',
  '$.ts.datetime("DD-MM-YYYY")'
);

3. Non-String or Unexpected Type Values

If a JSON field holds a number, boolean, or nested object instead of a string, datetime() will fail. This is common with heterogeneous JSON datasets.

-- FAILS: numeric value passed to datetime()
SELECT jsonb_path_query(
  '{"ts": 1723680000}',
  '$.ts.datetime()'
);
-- ERROR: 22031

-- SAFE: filter by type before calling datetime()
SELECT jsonb_path_query(data, '$.ts.datetime()')
FROM logs
WHERE jsonb_typeof(data->'ts') = 'string'
  AND data->>'ts' ~ '^\d{4}-\d{2}-\d{2}';

Quick Fix Solutions

Normalize dates before storing:

-- Fix non-ISO dates already in the table
UPDATE events
SET payload = jsonb_set(
  payload,
  '{ts}',
  to_jsonb(to_date(payload->>'ts', 'MM/DD/YYYY')::text)
)
WHERE payload->>'ts' ~ '^\d{2}/\d{2}/\d{4}$';

Wrap calls in a safe helper function:

CREATE OR REPLACE FUNCTION safe_json_datetime(p_data jsonb, p_path text)
RETURNS timestamptz LANGUAGE plpgsql AS $$
DECLARE v_result timestamptz;
BEGIN
  SELECT jsonb_path_query_first(p_data, p_path::jsonpath)::text::timestamptz
  INTO v_result;
  RETURN v_result;
EXCEPTION WHEN SQLSTATE '22031' THEN RETURN NULL;
END;
$$;

-- Usage
SELECT id, safe_json_datetime(payload, '$.ts.datetime()') FROM events;

Prevention Tips

Enforce format at insert time with a CHECK constraint:

ALTER TABLE events
ADD CONSTRAINT chk_ts_format
CHECK (
  payload->>'ts' IS NULL
  OR payload->>'ts' ~ '^\d{4}-\d{2}-\d{2}'
);

Profile your data before running datetime() queries in production:

SELECT
  COUNT(*) FILTER (WHERE payload->>'ts' ~ '^\d{4}-\d{2}-\d{2}') AS valid,
  COUNT(*) FILTER (WHERE payload->>'ts' !~ '^\d{4}-\d{2}-\d{2}'
                     AND payload->>'ts' IS NOT NULL)             AS invalid
FROM events;

Always validate data quality before deploying SQL/JSON path queries that rely on datetime(). Combining type guards (jsonb_typeof) with format regex checks will catch the vast majority of 22031 errors before they reach production.

Related Errors

22007 – invalid_datetime_format: Similar error but raised in standard SQL casting contexts (::timestamp, to_timestamp()).
22P02 – invalid_text_representation: Triggered when casting a JSON string value directly to a date/time type fails.

Oracle ORA-00913 Error: Causes and Solutions Complete Guide

umzzil nng — Tue, 16 Jun 2026 14:01:18 +0000

ORA-00913: Too Many Values — Causes, Fixes & Prevention

ORA-00913 is a common Oracle SQL parsing error that occurs when the number of values provided in a SQL statement exceeds the number of columns or expressions expected. Oracle detects this mismatch at parse time, meaning the statement fails immediately before any data is touched. Understanding the root causes helps you resolve and prevent this error efficiently.

Top 3 Causes

1. Mismatch in INSERT ... VALUES Statement

The most frequent cause: the number of values in the VALUES clause exceeds the number of columns listed in the INSERT statement.

-- ❌ Wrong: 3 columns specified, but 4 values provided
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (101, 'John Doe', 10, 'New York');

-- ✅ Fix 1: Remove the extra value
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (101, 'John Doe', 10);

-- ✅ Fix 2: Add the missing column to the column list
INSERT INTO employees (emp_id, emp_name, dept_id, location)
VALUES (101, 'John Doe', 10, 'New York');

2. Subquery Returns Too Many Columns

When a subquery used in a single-value context (e.g., = operator) returns more than one column, Oracle raises ORA-00913.

-- ❌ Wrong: Subquery returns two columns but only one is expected
SELECT *
FROM employees
WHERE dept_id = (SELECT dept_id, dept_name FROM departments WHERE location = 'NYC');

-- ✅ Fix 1: Limit the subquery to a single column
SELECT *
FROM employees
WHERE dept_id = (SELECT dept_id FROM departments WHERE location = 'NYC');

-- ✅ Fix 2: Use EXISTS for multi-column conditions
SELECT e.*
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.dept_id = e.dept_id
      AND d.location = 'NYC'
);

-- ✅ Fix 3: Use row value constructor for multi-column IN comparison
SELECT *
FROM employees
WHERE (dept_id, job_id) IN (
    SELECT dept_id, job_id FROM departments WHERE location = 'NYC'
);

3. INSERT INTO ... SELECT Column Count Mismatch

When using INSERT INTO ... SELECT, the SELECT clause returns more columns than the INSERT target specifies.

-- ❌ Wrong: SELECT returns 4 columns, INSERT expects 3
INSERT INTO emp_backup (emp_id, emp_name, dept_id)
SELECT emp_id, emp_name, dept_id, salary FROM employees;

-- ✅ Fix 1: Match SELECT columns to INSERT columns
INSERT INTO emp_backup (emp_id, emp_name, dept_id)
SELECT emp_id, emp_name, dept_id FROM employees;

-- ✅ Fix 2: Include all columns in both lists
INSERT INTO emp_backup (emp_id, emp_name, dept_id, salary)
SELECT emp_id, emp_name, dept_id, salary FROM employees;

-- ✅ Validation tip: Check column counts before bulk insert
SELECT COUNT(*) FROM all_tab_columns WHERE table_name = 'EMPLOYEES' AND owner = 'HR';
SELECT COUNT(*) FROM all_tab_columns WHERE table_name = 'EMP_BACKUP' AND owner = 'HR';

Quick Fix Checklist

Count columns in your INSERT target list and match them to VALUES or SELECT.
Never use SELECT * in INSERT INTO ... SELECT statements.
Reduce subquery SELECT to only the column(s) needed for comparison.
Use IN or EXISTS when comparing against multi-column subquery results.

Prevention Tips

Always use explicit column lists — Avoid INSERT INTO table VALUES (...) without a column list and avoid SELECT * in production DML. Explicit columns make your code resilient to schema changes and immediately expose mismatches during code review.
Validate DML in staging before production deployment — Whenever a DDL change (adding/dropping columns) is deployed, run a regression check on all INSERT statements and subqueries referencing the affected table. Use ALL_DEPENDENCIES to find dependent objects quickly.

Related Errors

Error Code	Description
ORA-00947	Not enough values — the opposite of ORA-00913
ORA-01427	Single-row subquery returns more than one row

ORA-00913 is about column count, while ORA-01427 is about row count — keep both in mind when writing subqueries.

PostgreSQL 22030 Error: Causes and Solutions Complete Guide

umzzil nng — Tue, 16 Jun 2026 10:03:00 +0000

PostgreSQL Error 22030: duplicate json object key value

PostgreSQL error code 22030 is raised when a JSON object contains duplicate keys, which violates the JSON specification (RFC 7159). This error most commonly appears when using jsonb_build_object(), json_object_agg(), or when inserting externally generated JSON strings that contain repeated keys into a jsonb column.

Top 3 Causes

1. Passing Duplicate Keys to `jsonb_build_object()`

This is the most frequent cause. Developers accidentally pass the same key twice when dynamically constructing JSON objects.

-- This will raise ERROR 22030
SELECT jsonb_build_object('user', 'Alice', 'score', 100, 'user', 'Bob');

-- Fix: Remove the duplicate key
SELECT jsonb_build_object('user', 'Alice', 'score', 100);

-- Or use distinct key names
SELECT jsonb_build_object('first_user', 'Alice', 'second_user', 'Bob', 'score', 100);

2. Inserting External JSON Strings with Duplicate Keys into `jsonb` Columns

External APIs or legacy systems sometimes produce JSON with repeated keys. While the json type accepts this, jsonb does not.

-- json type allows duplicate keys (no error)
SELECT '{"name": "Alice", "name": "Bob"}'::json;

-- Cast json to jsonb to auto-merge duplicate keys (last value wins)
SELECT '{"name": "Alice", "name": "Bob"}'::json::jsonb;
-- Result: {"name": "Bob"}

-- Safe insertion pattern for external data
INSERT INTO user_data (profile)
VALUES ('{"name": "Alice", "name": "Bob"}'::json::jsonb);

3. Duplicate Keys in `json_object_agg()` Aggregation

When aggregating rows where the key column contains duplicate values, PostgreSQL throws error 22030.

-- Sample data with duplicate keys
CREATE TABLE tags (id INT, k TEXT, v TEXT);
INSERT INTO tags VALUES (1,'color','red'),(1,'color','blue'),(1,'size','M');

-- ERROR: duplicate key value "color"
SELECT json_object_agg(k, v) FROM tags WHERE id = 1;

-- Fix: Deduplicate with DISTINCT ON before aggregating
SELECT json_object_agg(k, v)
FROM (
    SELECT DISTINCT ON (k) k, v
    FROM tags
    WHERE id = 1
    ORDER BY k, v
) deduped;

-- Alternative: Merge duplicate values into an array
SELECT json_object_agg(k, vals)
FROM (
    SELECT k, json_agg(v) AS vals
    FROM tags
    WHERE id = 1
    GROUP BY k
) grouped;

Quick Fix Solutions

Use json → jsonb casting to automatically resolve duplicate keys when handling external data.
Add DISTINCT ON in subqueries before calling json_object_agg().
Audit dynamic JSON construction code to ensure all keys are unique before passing them to build functions.

-- Universal safe pattern: normalize external JSON input
CREATE OR REPLACE FUNCTION safe_to_jsonb(input TEXT)
RETURNS JSONB AS $$
BEGIN
    RETURN input::json::jsonb;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Use it in queries
SELECT safe_to_jsonb('{"id": 1, "id": 2, "name": "test"}');
-- Result: {"id": 2, "name": "test"}

Prevention Tips

1. Normalize all external JSON input via json → jsonb casting before storing it. This automatically resolves duplicate keys by keeping the last value, preventing runtime errors in production pipelines.

2. Validate and deduplicate keys before aggregation. Always use a CTE or subquery with DISTINCT ON or GROUP BY when using json_object_agg() on potentially dirty data sets.

-- Recommended pattern for safe JSON aggregation
WITH deduped AS (
    SELECT DISTINCT ON (key_col) key_col, val_col
    FROM source_table
    ORDER BY key_col, updated_at DESC
)
SELECT json_object_agg(key_col, val_col) FROM deduped;

Related Errors

22032 (invalid_json_text) — Malformed JSON syntax; often appears alongside 22030 when handling raw external JSON.
23505 (unique_violation) — Triggered by duplicate values in unique-indexed columns, a related uniqueness constraint issue.

Oracle ORA-00911 Error: Causes and Solutions Complete Guide

umzzil nng — Tue, 16 Jun 2026 10:01:37 +0000

ORA-00911: Invalid Character — Causes, Fixes & Prevention

ORA-00911 is one of Oracle's most commonly encountered errors, triggered when a SQL statement contains a character that Oracle's SQL parser does not recognize as valid. This typically happens when illegal characters such as semicolons, invisible Unicode characters, or full-width special characters are present in the SQL string. Understanding the root cause quickly can save significant debugging time in production environments.

Top 3 Causes & SQL Examples

1. Trailing Semicolon in Application Code

The most frequent cause of ORA-00911 is including a semicolon (;) at the end of a SQL statement when executing it through a database driver such as JDBC, cx_Oracle, or ODP.NET. While SQL*Plus and SQL Developer use the semicolon as a statement terminator, database drivers treat it as part of the SQL string itself, causing the parser to fail.

Incorrect (causes ORA-00911):

-- This will fail when executed via JDBC or cx_Oracle
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id = 50;

Correct (remove the semicolon):

-- No semicolon at the end when using a DB driver
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id = 50

2. Invisible or Non-ASCII Characters in SQL

When SQL is copied from word processors, web browsers, email clients, or markdown editors, invisible Unicode characters (e.g., non-breaking space \u00A0, line separator \u2028, or BOM characters) can silently embed themselves into the SQL string. These characters are invisible to the human eye but are immediately flagged by Oracle's parser.

Detecting hidden characters using DUMP:

-- Use DUMP to inspect character codes in a string
SELECT DUMP('SELECT * FROM dual', 16) AS char_codes
FROM dual;

-- Remove non-printable and non-ASCII characters using REGEXP_REPLACE
SELECT REGEXP_REPLACE(
           :sql_input,
           '[^\x09\x0A\x0D\x20-\x7E]',  -- Keep tab, newline, CR, and printable ASCII
           ''
       ) AS cleaned_sql
FROM dual;

Always paste SQL into a plain text editor (e.g., Notepad, VSCode with plain text mode) to strip hidden formatting before using it in your application.

3. Full-Width or Invalid Special Characters

In environments where input method editors (IMEs) are used — particularly for East Asian languages — it is easy to accidentally insert full-width characters (e.g., ， instead of ,, or " instead of ") into SQL statements. Oracle's parser does not accept these characters and immediately raises ORA-00911.

Incorrect (full-width comma — causes ORA-00911):

-- Full-width comma and quotation marks will break parsing
SELECT employee_id，first_name，last_name
FROM employees;

Correct (standard ASCII punctuation):

-- Use standard half-width ASCII characters only
SELECT employee_id, first_name, last_name
FROM employees;

-- Column aliases with spaces must use standard double quotes
SELECT
    employee_id  AS "Employee ID",
    first_name   AS "First Name",
    hire_date    AS "Hire Date"
FROM employees
WHERE ROWNUM <= 5;

Quick Fix Solutions

Symptom	Fix
SQL works in SQL*Plus but fails in app	Remove trailing semicolon
SQL copied from browser/email fails	Strip non-ASCII chars with `REGEXP_REPLACE`
Error after typing SQL with IME enabled	Replace all punctuation with ASCII equivalents

A simple PL/SQL utility to sanitize SQL strings before execution:

CREATE OR REPLACE FUNCTION clean_sql(p_sql IN VARCHAR2)
RETURN VARCHAR2
IS
    v_sql VARCHAR2(32767);
BEGIN
    v_sql := TRIM(p_sql);
    -- Remove trailing semicolon
    IF SUBSTR(v_sql, -1) = ';' THEN
        v_sql := SUBSTR(v_sql, 1, LENGTH(v_sql) - 1);
    END IF;
    -- Strip non-ASCII characters (preserve tab, newline, carriage return)
    v_sql := REGEXP_REPLACE(v_sql, '[^\x09\x0A\x0D\x20-\x7E]', '');
    RETURN TRIM(v_sql);
END clean_sql;
/

-- Test the function
SELECT clean_sql('SELECT * FROM employees;') AS result FROM dual;

Prevention Tips

1. Enforce a No-Semicolon Policy in Application SQL
Establish a team coding standard that prohibits trailing semicolons in SQL strings passed to database drivers. Integrate a SQL linter (e.g., SQLFluff) into your CI/CD pipeline to automatically catch these issues before deployment.

2. Always Use Plain-Text Editors for SQL Authoring
Never write or paste SQL directly from rich-text sources. Use editors configured to save files as UTF-8 without BOM, and leverage the "Paste as Plain Text" option (Ctrl+Shift+V) to avoid embedding invisible formatting characters.

Related Oracle Errors

ORA-00900 — Invalid SQL statement (general syntax failure)
ORA-01756 — Quoted string not properly terminated (mismatched quotes)
ORA-00907 — Missing right parenthesis (bracket mismatch)
ORA-00936 — Missing expression (incomplete SQL structure)

Oracle ORA-00910 Error: Causes and Solutions Complete Guide

umzzil nng — Tue, 16 Jun 2026 06:01:34 +0000

ORA-00910: Specified Length Too Long for Its Datatype

ORA-00910 is thrown by Oracle when you declare a column or variable with a length that exceeds the maximum allowed for that data type. This most commonly occurs during CREATE TABLE or ALTER TABLE statements, but can also appear in PL/SQL variable declarations. Understanding Oracle's hard limits per data type is the fastest way to resolve and prevent this error.

Top 3 Causes

1. VARCHAR2 or CHAR Length Exceeds Oracle's Limit

Oracle limits VARCHAR2 columns to 4,000 bytes (standard) and CHAR to 2,000 bytes. Developers migrating schemas from MySQL or SQL Server frequently hit this wall because those databases allow larger string columns.

-- Causes ORA-00910
CREATE TABLE orders (
    notes VARCHAR2(5000)   -- Exceeds 4000-byte limit
);

-- Fix: Reduce length or switch to CLOB
CREATE TABLE orders (
    notes VARCHAR2(4000)   -- Within limit
);

-- Fix: Use CLOB for large text
CREATE TABLE orders (
    notes CLOB
);

-- Check current MAX_STRING_SIZE setting
SELECT name, value
FROM v$parameter
WHERE name = 'max_string_size';

2. NVARCHAR2 or NCHAR Exceeds Character Limit

NVARCHAR2 has a maximum of 2,000 characters and NCHAR allows only 1,000 characters. Because these types use the National Character Set (typically AL16UTF16), the byte overhead is higher — Oracle enforces character-count limits rather than byte limits here.

-- Causes ORA-00910
CREATE TABLE translations (
    content NVARCHAR2(4000)  -- Exceeds 2000-character limit
);

-- Fix: Stay within 2000 characters
CREATE TABLE translations (
    content NVARCHAR2(2000)
);

-- Fix: Use NCLOB for large national character data
CREATE TABLE translations (
    content NCLOB
);

-- Verify National Character Set
SELECT parameter, value
FROM nls_database_parameters
WHERE parameter = 'NLS_NCHAR_CHARACTERSET';

3. Incorrect Length in PL/SQL Variable Declaration

PL/SQL variables follow slightly different rules: VARCHAR2 in PL/SQL allows up to 32,767 bytes, but any value above that still triggers ORA-00910. Hardcoded lengths in package specs or dynamic SQL generators are common culprits.

-- Causes ORA-00910 (exceeds PL/SQL VARCHAR2 max)
DECLARE
    v_buffer VARCHAR2(40000);
BEGIN
    NULL;
END;
/

-- Fix: Cap at 32767 or use CLOB
DECLARE
    v_buffer VARCHAR2(32767);
BEGIN
    DBMS_OUTPUT.PUT_LINE('OK');
END;
/

-- Best Fix: Use %TYPE to inherit column definition automatically
DECLARE
    v_notes orders.notes%TYPE;  -- Inherits type and length from column
BEGIN
    NULL;
END;
/

Quick Fix Summary

Data Type	Table Column Max	PL/SQL Variable Max	Large Data Alternative
VARCHAR2	4,000 bytes	32,767 bytes	CLOB
CHAR	2,000 bytes	32,767 bytes	CLOB
NVARCHAR2	2,000 chars	32,767 bytes	NCLOB
NCHAR	1,000 chars	32,767 bytes	NCLOB

-- Alter an existing column to CLOB if data has grown too large
ALTER TABLE orders MODIFY (notes CLOB);

-- Inspect columns near the limit in your schema
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
WHERE (data_type = 'VARCHAR2'  AND data_length >= 3900)
   OR (data_type = 'NVARCHAR2' AND char_length >= 1900)
ORDER BY table_name;

Prevention Tips

Standardize your type mapping before migration. Always review Oracle's data type limits when porting schemas from other databases. Use %TYPE and %ROWTYPE in PL/SQL to avoid hardcoding lengths that may drift out of sync with DDL changes.
Enable MAX_STRING_SIZE = EXTENDED only if truly needed. Oracle 12c+ allows VARCHAR2 up to 32,767 bytes at the table level with this parameter, but it is irreversible and requires running utl32k.sql. Evaluate whether CLOB is a simpler, safer alternative before making this system-wide change.

Related Errors

ORA-01401 – inserted value too large for column: runtime error when DML data exceeds column length.
ORA-06502 – character string buffer too small: PL/SQL runtime error when assigned value overflows a variable.
ORA-00972 – identifier is too long: object name exceeds the allowed length (30 chars pre-12.2, 128 chars from 12.2+).

PostgreSQL 2200S Error: Causes and Solutions Complete Guide

umzzil nng — Tue, 16 Jun 2026 00:03:08 +0000

PostgreSQL Error 2200S: invalid xml comment

PostgreSQL error 2200S (invalid_xml_comment) is raised when an XML comment embedded in XML data violates the W3C XML 1.0 specification. This most commonly occurs when using XML-related functions such as xmlcomment(), XMLPARSE(), or when inserting data into an XML type column. Understanding the exact rules governing XML comments will save you significant debugging time in production environments.

Top 3 Causes

1. Double Hyphens (`--`) Inside an XML Comment

The XML 1.0 spec explicitly forbids the sequence -- anywhere inside an XML comment body. This catches many developers off guard, especially those accustomed to SQL-style -- comments.

-- This will FAIL: double hyphen inside comment
SELECT xmlcomment('this is -- invalid');
-- ERROR:  invalid xml comment  (SQLSTATE 2200S)

-- This works: replace '--' with '- -'
SELECT xmlcomment('this is - - valid');

-- Safe dynamic comment generation
SELECT xmlcomment(
    replace(user_input_value, '--', '- -')
)
FROM (SELECT 'status -- pending' AS user_input_value) t;

2. Malformed Comment Closing Tag

An XML comment must close with exactly -->. Using --->, ---->, or omitting the closing tag entirely causes the parser to throw 2200S.

-- FAIL: extra hyphen before closing '>'
SELECT XMLPARSE(DOCUMENT
    '<?xml version="1.0"?><!--- bad close --->
     <root/>');
-- ERROR:  invalid xml comment

-- PASS: standard well-formed comment
SELECT XMLPARSE(DOCUMENT
    '<?xml version="1.0"?><!-- good comment -->
     <root><item>data</item></root>');

-- Inserting a valid XML document with a comment
INSERT INTO reports (doc)
VALUES (XMLPARSE(DOCUMENT
    '<?xml version="1.0" encoding="UTF-8"?>
     <!-- Generated: 2024-01-15 -->
     <report><title>Q1</title></report>'));

3. Unsanitized User Input Injected into XML Comments

When raw user input or external API data is interpolated directly into XML comment strings, any embedded -- sequence will trigger this error. This is a common bug in ETL pipelines and dynamic XML builders.

-- Helper function to sanitize text before using in XML comments
CREATE OR REPLACE FUNCTION safe_xml_comment(p_text TEXT)
RETURNS XML
LANGUAGE plpgsql AS $$
BEGIN
    -- Strip double hyphens and trailing hyphens
    RETURN xmlcomment(
        rtrim(replace(p_text, '--', '- -'), '-')
    );
END;
$$;

-- Usage
SELECT safe_xml_comment('user note: value--123');
-- Returns: <!-- user note: value- -123 -->

-- Catch the error gracefully in PL/pgSQL
DO $$
BEGIN
    PERFORM xmlcomment('bad--input');
EXCEPTION
    WHEN invalid_xml_comment THEN
        RAISE NOTICE 'Caught SQLSTATE %, cleaning input...', SQLSTATE;
END;
$$;

Quick Fix Solutions

-- 1. Validate existing XML column for problematic comments
SELECT id
FROM xml_documents
WHERE content::TEXT ~ '--(?!>)';

-- 2. Bulk-fix stored XML by replacing double hyphens
UPDATE xml_documents
SET content = XMLPARSE(DOCUMENT
    replace(content::TEXT, '-->', ' -->')  -- adjust as needed
)
WHERE content::TEXT ~ '--(?!>)';

-- 3. Use xmlcomment() instead of manual string concat
-- BAD:
SELECT ('<!-- my comment -->'::XML);

-- GOOD:
SELECT xmlcomment('my comment');

Prevention Tips

1. Always use xmlcomment() for generating XML comments. Never build XML comment strings through raw concatenation. PostgreSQL's built-in xmlcomment() function still requires valid input, but pairing it with a sanitizer function makes the intent explicit and auditable.

2. Add a BEFORE INSERT/UPDATE trigger on XML columns. Let the database enforce XML validity as a last line of defense, independent of application logic.

CREATE OR REPLACE FUNCTION trg_validate_xml()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    -- Force re-parse to catch any malformed XML including bad comments
    PERFORM XMLPARSE(DOCUMENT NEW.content::TEXT);
    RETURN NEW;
EXCEPTION
    WHEN invalid_xml_comment THEN
        RAISE EXCEPTION 'Invalid XML comment in input. SQLSTATE: 2200S';
END;
$$;

CREATE TRIGGER validate_xml_before_write
BEFORE INSERT OR UPDATE ON xml_documents
FOR EACH ROW EXECUTE FUNCTION trg_validate_xml();

Related Errors

SQLSTATE	Name	When It Occurs
2200M	invalid_xml_document	XML is not well-formed at the document level
2200N	invalid_xml_content	XML content (not comment) fails validation
2200T	invalid_xml_processing_instruction	Malformed `<?target data?>` PI node
22000	data_exception	Parent class for all XML and data errors

Oracle ORA-00909 Error: Causes and Solutions Complete Guide

umzzil nng — Tue, 16 Jun 2026 00:01:32 +0000

ORA-00909: Invalid Number of Arguments — Causes, Fixes, and Prevention

ORA-00909 is a parse-time error thrown by Oracle Database when a built-in or user-defined function is called with the wrong number of arguments. Because it occurs during the SQL parsing phase — before any data is actually accessed — the query fails immediately and no rows are returned. This error is common among developers who work across multiple database platforms or those who call PL/SQL functions whose signatures have changed.

Top 3 Causes

1. Wrong Argument Count for a Built-in Function

The most frequent cause is simply passing too few or too many arguments to Oracle built-in functions like NVL, SUBSTR, ROUND, or DECODE.

-- WRONG: NVL requires exactly 2 arguments
SELECT NVL(employee_name)
FROM employees;
-- ORA-00909: invalid number of arguments

-- WRONG: ROUND accepts only 1 or 2 arguments
SELECT ROUND(salary, 2, 'UP')
FROM employees;
-- ORA-00909: invalid number of arguments

-- CORRECT
SELECT NVL(employee_name, 'N/A') FROM employees;
SELECT ROUND(salary, 2)          FROM employees;
SELECT SUBSTR(last_name, 1, 5)   FROM employees;

2. Calling a User-Defined Function After a Signature Change

When a PL/SQL function or package procedure is modified — parameters added, removed, or reordered — any calling SQL or application code that isn't updated will trigger ORA-00909.

-- Check current parameter spec before calling
SELECT argument_name,
       position,
       data_type,
       in_out,
       defaulted
FROM   all_arguments
WHERE  object_name = 'GET_EMPLOYEE_INFO'  -- uppercase
AND    owner       = 'HR'
ORDER BY position;

-- WRONG: function now requires 2 params, but only 1 is passed
SELECT get_employee_info(101)
FROM dual;
-- ORA-00909: invalid number of arguments

-- CORRECT: pass all required arguments
SELECT get_employee_info(101, 'FULL')
FROM dual;

3. Dynamic SQL Building Incorrect Function Calls at Runtime

In dynamic SQL scenarios using EXECUTE IMMEDIATE or DBMS_SQL, argument values can be accidentally omitted during string concatenation, causing ORA-00909 only under specific runtime conditions.

DECLARE
    v_col    VARCHAR2(50)   := 'SALARY';
    v_defval VARCHAR2(50)   := '0';
    v_sql    VARCHAR2(1000);
    v_result NUMBER;
BEGIN
    -- WRONG: second argument for NVL is missing
    -- v_sql := 'SELECT NVL(' || v_col || ') FROM employees WHERE ROWNUM = 1';

    -- CORRECT: build the full, valid function call
    v_sql := 'SELECT NVL(' || v_col || ', ' || v_defval || ') '
          || 'FROM employees WHERE ROWNUM = 1';

    DBMS_OUTPUT.PUT_LINE('SQL: ' || v_sql);  -- log before execution
    EXECUTE IMMEDIATE v_sql INTO v_result;
    DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Failed SQL: ' || v_sql);
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        RAISE;
END;
/

Quick Fix Checklist

Identify the offending function — read the full error stack; Oracle usually points to the line number.
Check the correct signature — use ALL_ARGUMENTS or consult the Oracle SQL Language Reference.
Count your arguments — compare what you passed against what the function expects.
For dynamic SQL — always DBMS_OUTPUT.PUT_LINE the assembled string before executing it.

Prevention Tips

Use an IDE with live syntax validation — tools like SQL Developer, Toad, or DBeaver highlight argument mismatches before you run the query.
Version-control function signatures — whenever a PL/SQL function's parameter list changes, run an impact analysis against ALL_ARGUMENTS and update all callers as part of the same release.

-- Find all objects that call a specific function (quick impact check)
SELECT name, type, line, text
FROM   all_source
WHERE  UPPER(text) LIKE '%GET_EMPLOYEE_INFO%'
AND    owner = 'HR'
ORDER BY name, line;

Related Errors

Error Code	Message	Relationship
ORA-00907	missing right parenthesis	Often co-occurs with argument typos
ORA-00904	invalid identifier	Mistyped function name near argument issues
ORA-06553 (PLS-306)	wrong number or types of arguments	PL/SQL equivalent of ORA-00909

PostgreSQL 2200N Error: Causes and Solutions Complete Guide

umzzil nng — Mon, 15 Jun 2026 14:02:51 +0000

PostgreSQL Error 2200N: invalid xml content

PostgreSQL error 2200N: invalid xml content occurs when you attempt to insert or process malformed XML data into an XML type column or pass invalid XML to XML-related functions. PostgreSQL strictly validates XML data against the W3C XML standard, and any deviation — from mismatched tags to illegal characters — triggers this error. It most commonly appears when ingesting XML from external APIs, legacy systems, or dynamically building XML strings in application code.

Top 3 Causes & SQL Examples

1. Mismatched or Improperly Nested Tags

XML requires every opening tag to have a matching closing tag in the correct order.

-- This will fail: tags are improperly nested
INSERT INTO orders (id, xml_data)
VALUES (1, XMLPARSE(DOCUMENT '<order><item>Apple</order></item>'));
-- ERROR:  invalid xml content

-- Correct version
INSERT INTO orders (id, xml_data)
VALUES (1, XMLPARSE(DOCUMENT '<order><item>Apple</item></order>'));

2. Unescaped Special Characters

Characters like &, <, and > must be escaped as XML entities (&, <, >). Passing them raw inside XML text nodes will break parsing immediately.

-- This will fail: unescaped ampersand
SELECT XMLPARSE(DOCUMENT '<company><name>AT&T</name></company>');
-- ERROR:  invalid xml content

-- Fix 1: Use xmlelement() — it escapes automatically
SELECT xmlelement(name company,
           xmlelement(name name, 'AT&T')
       );
-- Result: <company><name>AT&amp;T</name></company>

-- Fix 2: Manual escape before parsing
SELECT XMLPARSE(DOCUMENT
    replace(raw_xml, '&', '&amp;')
)
FROM staging_table
WHERE id = 1;

3. Multiple Root Elements or Missing Root

A valid XML document must have exactly one root element. Concatenating XML fragments without a wrapper is a very common mistake.

-- This will fail: two root elements
SELECT XMLPARSE(DOCUMENT '<item>A</item><item>B</item>');
-- ERROR:  invalid xml content

-- Fix: wrap fragments in a single root
SELECT XMLPARSE(DOCUMENT '<items><item>A</item><item>B</item></items>');

-- Safely aggregate multiple rows into one XML document
WITH fragments AS (
    SELECT '<item>' || product_name || '</item>' AS frag
    FROM products
    WHERE category = 'fruit'
)
SELECT XMLPARSE(DOCUMENT
    '<items>' || string_agg(frag, '') || '</items>'
)
FROM fragments;

Quick Fix Solutions

Create a validation helper function to identify bad rows before they cause errors in production:

-- Validation helper function
CREATE OR REPLACE FUNCTION is_valid_xml(p_text TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    PERFORM XMLPARSE(DOCUMENT p_text);
    RETURN TRUE;
EXCEPTION
    WHEN invalid_xml_content THEN
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Find all invalid XML rows in a staging table
SELECT id, raw_xml
FROM staging_xml_data
WHERE is_valid_xml(raw_xml) = FALSE;

-- Safe load: only move valid rows to production
INSERT INTO production_table (id, xml_data)
SELECT id, XMLPARSE(DOCUMENT raw_xml)
FROM staging_xml_data
WHERE is_valid_xml(raw_xml) = TRUE;

Prevention Tips

1. Use PostgreSQL's built-in XML functions instead of string concatenation.
Functions like xmlelement(), xmlforest(), and xmlagg() automatically escape special characters and guarantee well-formed output, eliminating the most common source of this error.

-- Preferred: built-in functions handle escaping for you
SELECT xmlelement(name report,
           xmlattributes(NOW() AS generated_at),
           xmlforest(
               customer_name AS customer,
               total_amount  AS total
           )
       )
FROM orders;

2. Validate XML at the staging layer before promoting to production.
Always land raw XML into a TEXT staging column first, run is_valid_xml() batch checks, and only promote verified data to your XML typed production columns. This two-stage pipeline prevents invalid data from ever reaching live tables and makes debugging far easier.

Related Errors

2200M (invalid XML document) — Similar error; the document structure is recognized but content violates XML rules.
22000 (data exception) — Parent error class for all XML and data-type violations.
42804 (datatype mismatch) — Raised when inserting TEXT into an XML column without an explicit cast.

Oracle ORA-00907 Error: Causes and Solutions Complete Guide

umzzil nng — Mon, 15 Jun 2026 14:01:24 +0000

ORA-00907: Missing Right Parenthesis — Causes, Fixes & Prevention

ORA-00907 is one of Oracle's most common SQL parsing errors, triggered when the Oracle engine encounters an opening parenthesis ( without a matching closing parenthesis ), or when a parenthesis appears in a syntactically invalid position. This error can stem from simple typos to complex subquery structural mistakes, and it can be especially tricky to diagnose in long SQL statements.

Top 3 Causes

1. Missing Parenthesis in DDL Statements

Forgetting to close parentheses in CREATE TABLE column definitions or constraint declarations is the most frequent cause.

Incorrect:

-- Missing closing parenthesis in VARCHAR2 and CHECK constraint
CREATE TABLE products (
    product_id   NUMBER,
    product_name VARCHAR2(200,       -- missing closing )
    price        NUMBER(10, 2),
    CONSTRAINT chk_price CHECK price > 0  -- missing parentheses around condition
);

Correct:

CREATE TABLE products (
    product_id   NUMBER,
    product_name VARCHAR2(200),
    price        NUMBER(10, 2),
    CONSTRAINT chk_price CHECK (price > 0)
);

2. Unclosed Subquery or Inline View

Inline views, WHERE clause subqueries, and CTEs all require carefully matched parentheses. Dropping even one closing ) breaks the entire query.

Incorrect:

-- Missing closing parenthesis for the inline view
SELECT a.emp_name, b.avg_sal
FROM employees a,
     (SELECT dept_id, AVG(salary) AS avg_sal
      FROM employees
      GROUP BY dept_id    -- missing ) and alias here
WHERE a.dept_id = b.dept_id;

Correct:

SELECT a.emp_name, b.avg_sal
FROM employees a,
     (SELECT dept_id, AVG(salary) AS avg_sal
      FROM employees
      GROUP BY dept_id) b
WHERE a.dept_id = b.dept_id;

3. Syntax Errors in CASE, IN, or Nested Functions

When CASE expressions, IN lists with subqueries, or deeply nested function calls are involved, a misplaced or missing parenthesis raises ORA-00907.

Incorrect:

-- Missing closing parenthesis on the IN subquery
SELECT emp_name, salary
FROM employees
WHERE dept_id IN (
    SELECT dept_id
    FROM departments
    WHERE location_id = 100
-- closing ) is missing
AND salary > 3000;

Correct:

SELECT emp_name, salary
FROM employees
WHERE dept_id IN (
    SELECT dept_id
    FROM departments
    WHERE location_id = 100
)
AND salary > 3000;

Quick Fix Checklist

-- Step 1: Count opening vs closing parentheses (they must match)
SELECT
    LENGTH(:your_sql) - LENGTH(REPLACE(:your_sql, '(', '')) AS open_parens,
    LENGTH(:your_sql) - LENGTH(REPLACE(:your_sql, ')', '')) AS close_parens
FROM DUAL;

-- Step 2: Test subqueries independently before combining
SELECT dept_id
FROM departments
WHERE location_id = 100;  -- validate this first, then wrap it

-- Step 3: Use proper indentation to visually track nesting
SELECT *
FROM (                          -- level 1 open
    SELECT dept_id, COUNT(*) cnt
    FROM (                      -- level 2 open
        SELECT dept_id
        FROM employees
        WHERE salary > 5000
    )                           -- level 2 close
    GROUP BY dept_id
)                               -- level 1 close
WHERE cnt > 5;

Prevention Tips

Use an IDE with bracket matching. Tools like SQL Developer, Toad, or DBeaver highlight matching parentheses in real time, making it easy to spot mismatches before execution.

Write parentheses in pairs immediately. When you type (, immediately type ) and then fill in the content between them. This simple habit virtually eliminates missing-parenthesis errors in your daily SQL writing.

Related Oracle Errors

Error Code	Description
ORA-00906	Missing left parenthesis
ORA-00933	SQL command not properly ended
ORA-00936	Missing expression
ORA-00902	Invalid datatype

PostgreSQL 2200M Error: Causes and Solutions Complete Guide

umzzil nng — Mon, 15 Jun 2026 10:02:48 +0000

PostgreSQL Error 2200M: invalid xml document

PostgreSQL error 2200M: invalid xml document is raised when an XML value passed to XMLPARSE(DOCUMENT ...) or inserted into an xml-typed column does not conform to the W3C XML specification for a well-formed document. Unlike 2200N: invalid xml content, this error specifically targets document-level structural violations, such as missing or multiple root elements. It commonly appears during data migrations, API integrations, or when dynamically building XML strings in application code.

Top 3 Causes & SQL Examples

1. Multiple or Missing Root Elements

An XML document must have exactly one root element. Passing fragments or multiple top-level elements will immediately trigger this error.

-- Bad: multiple root elements
SELECT XMLPARSE(DOCUMENT '<item>A</item><item>B</item>');
-- ERROR:  invalid xml document

-- Bad: no root element at all
SELECT XMLPARSE(DOCUMENT 'just some text');
-- ERROR:  invalid xml document

-- Good: wrap everything in a single root
SELECT XMLPARSE(DOCUMENT '<root><item>A</item><item>B</item></root>');

2. Unescaped Special Characters or Unclosed Tags

Characters like &, <, and > must be escaped as XML entities. Unclosed tags are also a common culprit when XML is built by string concatenation.

-- Bad: unescaped ampersand
SELECT XMLPARSE(DOCUMENT '<root><price>10 & 20</price></root>');
-- ERROR:  invalid xml document

-- Bad: unclosed tag
SELECT XMLPARSE(DOCUMENT '<root><item>value</root>');
-- ERROR:  invalid xml document

-- Good: use PostgreSQL built-in XML functions (auto-escaping)
SELECT XMLELEMENT(NAME "root",
    XMLELEMENT(NAME "price", '10 & 20')
);

-- Good: manually escape entities
SELECT XMLPARSE(DOCUMENT '<root><price>10 &amp; 20</price></root>');

3. Encoding Declaration Mismatch

An <?xml version="1.0" encoding="..."> declaration that does not match the actual database encoding will cause parsing to fail.

-- Check your database encoding first
SHOW server_encoding;
-- e.g., UTF8

-- Bad: declared encoding differs from DB encoding
SELECT XMLPARSE(DOCUMENT
    '<?xml version="1.0" encoding="EUC-KR"?><root>data</root>'
);
-- May raise: invalid xml document

-- Good: match the declaration to your DB encoding
SELECT XMLPARSE(DOCUMENT
    '<?xml version="1.0" encoding="UTF-8"?><root>data</root>'
);

-- Good: omit the declaration entirely
SELECT XMLPARSE(DOCUMENT '<root>data</root>');

-- Utility function to strip problematic XML declarations
CREATE OR REPLACE FUNCTION strip_xml_declaration(p_xml TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN regexp_replace(p_xml, '^\s*<\?xml[^?]*\?>\s*', '', 'i');
END;
$$ LANGUAGE plpgsql;

Quick Fix Solutions

Use this validation helper before inserting any XML data:

-- Safe validation wrapper
CREATE OR REPLACE FUNCTION is_valid_xml(p_text TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    PERFORM XMLPARSE(DOCUMENT p_text);
    RETURN TRUE;
EXCEPTION
    WHEN invalid_xml_document THEN RETURN FALSE;
    WHEN invalid_xml_content  THEN RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Filter out bad rows before bulk insert
INSERT INTO xml_store (payload)
SELECT XMLPARSE(DOCUMENT raw_xml)
FROM staging_table
WHERE is_valid_xml(raw_xml);

Prevention Tips

Always use PostgreSQL's native XML builder functions (XMLELEMENT, XMLFOREST, XMLAGG) instead of string concatenation. These functions handle escaping and structure automatically, eliminating the most common sources of malformed documents.
Add a CHECK constraint on XML columns and validate incoming data at the pipeline boundary before it reaches the database.

-- Add constraint using the validation function
ALTER TABLE xml_store
ADD CONSTRAINT chk_valid_xml_doc
CHECK (is_valid_xml(payload::text));

-- Prefer the native xml type over text for automatic parse-time validation
CREATE TABLE documents (
    id      SERIAL PRIMARY KEY,
    content XML NOT NULL  -- PostgreSQL validates on insert automatically
);

Related Errors

Code	Name	Notes
`2200N`	invalid xml content	Triggered by `XMLPARSE(CONTENT ...)` with structural issues
`22000`	data exception	Parent category for all data-related exceptions
`42804`	datatype mismatch	Occurs when a non-XML value is cast to the `xml` type incorrectly

Oracle ORA-00906 Error: Causes and Solutions Complete Guide

umzzil nng — Mon, 15 Jun 2026 10:01:21 +0000

ORA-00906: Missing Left Parenthesis — Causes, Fixes & Prevention

ORA-00906 is a syntax error thrown by Oracle when the parser expects a left parenthesis ( at a specific point in a SQL statement but finds something else — or nothing at all. This error can appear in DDL statements like CREATE TABLE, DML statements like INSERT or SELECT, and even in function calls or subqueries. It is one of the most common Oracle errors and is almost always straightforward to fix once you identify the exact location.

Top 3 Causes

1. Missing Parentheses in CREATE TABLE or CREATE INDEX

When defining a table, Oracle requires that the column list be wrapped in parentheses immediately after the table name. Forgetting this — especially when migrating scripts from MySQL or SQL Server — instantly triggers ORA-00906.

-- WRONG: No parentheses around column definitions
CREATE TABLE employees
    emp_id   NUMBER(10),
    emp_name VARCHAR2(100)
;

-- CORRECT: Wrap column list in parentheses
CREATE TABLE employees
(
    emp_id   NUMBER(10)    NOT NULL,
    emp_name VARCHAR2(100) NOT NULL,
    CONSTRAINT pk_emp PRIMARY KEY (emp_id)
);

-- WRONG: Missing parentheses in CREATE INDEX
CREATE INDEX idx_name ON employees emp_name;

-- CORRECT
CREATE INDEX idx_name ON employees (emp_name);

2. Missing Parentheses in Function Calls or IN Clauses

Oracle built-in functions such as NVL, TO_DATE, and SUBSTR require parentheses immediately after the function name. Similarly, the IN operator must be followed by a parenthesized list or subquery.

-- WRONG: NVL called without parentheses
SELECT NVL emp_name, 'N/A' FROM employees;

-- CORRECT
SELECT NVL(emp_name, 'N/A') AS emp_name
FROM employees;

-- WRONG: IN clause without parentheses
SELECT * FROM employees
WHERE dept_id IN 10, 20, 30;

-- CORRECT
SELECT * FROM employees
WHERE dept_id IN (10, 20, 30);

3. Missing Parentheses Around Subqueries or CTE Definitions

Inline views (derived tables) and subqueries used inside IN, FROM, or WHERE clauses must be enclosed in parentheses. The same applies to CTE blocks defined with the WITH clause.

-- WRONG: Inline view without parentheses
SELECT a.dept_id, a.avg_sal
FROM SELECT dept_id, AVG(salary) avg_sal
     FROM employees GROUP BY dept_id a;

-- CORRECT
SELECT a.dept_id, a.avg_sal
FROM (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY dept_id
) a;

-- WRONG: WITH clause subquery missing parentheses
WITH summary AS
    SELECT dept_id, COUNT(*) cnt FROM employees GROUP BY dept_id
SELECT * FROM summary;

-- CORRECT
WITH summary AS (
    SELECT dept_id, COUNT(*) AS cnt
    FROM employees
    GROUP BY dept_id
)
SELECT * FROM summary;

Quick Fix Checklist

When you hit ORA-00906, run through this checklist:

Check your CREATE TABLE / INDEX statements — is the column or key list wrapped in ()?
Check every function call — does each function have ( right after its name?
Check IN clauses — is the value list or subquery enclosed in ()?
Check inline views and CTEs — is the subquery body fully wrapped in ()?
Count parentheses pairs — use your IDE's bracket-matching feature to spot mismatches quickly.

Prevention Tips

Use an IDE with real-time syntax checking.
Tools like Oracle SQL Developer, DBeaver, or Toad highlight unmatched or missing parentheses as you type. Always validate SQL in your IDE before deploying to production. Integrating a linter like SQLFluff into your CI/CD pipeline adds an extra layer of protection for the whole team.

Maintain a SQL migration checklist when switching databases.
If you're porting scripts from MySQL, PostgreSQL, or SQL Server to Oracle, keep a checklist of Oracle-specific syntax requirements — especially mandatory parentheses in CREATE TABLE, CREATE INDEX, function calls, and subqueries. Pair programming or peer code review before any DDL deployment can catch ORA-00906 and similar syntax errors before they ever reach production.

Related Errors

ORA-00907 — Missing right parenthesis; the closing ) counterpart to ORA-00906.
ORA-00936 — Missing expression; often appears alongside ORA-00906 when the parser loses track of an expression due to a missing bracket.
ORA-00917 — Missing comma; can co-occur when both a comma and a parenthesis are omitted in a column or value list.

PostgreSQL 2200L Error: Causes and Solutions Complete Guide

umzzil nng — Mon, 15 Jun 2026 06:02:52 +0000

PostgreSQL Error 2200L: not an xml document

PostgreSQL error 2200L occurs when a string or value passed to an XML function or cast to the xml type does not conform to the structure of a valid XML document. Unlike an XML fragment (which may have multiple root nodes), an XML document must have exactly one root element and be well-formed. This error is common when working with XMLPARSE, xpath(), xmltable(), or direct ::xml casting.

Top 3 Causes

1. Parsing an XML Fragment as a Document

PostgreSQL distinguishes between DOCUMENT and CONTENT modes. Passing multiple root nodes or a rootless string to XMLPARSE(DOCUMENT ...) will trigger this error.

-- ERROR: not an xml document (multiple root nodes)
SELECT XMLPARSE(DOCUMENT '<item>apple</item><item>banana</item>');

-- FIX 1: Use CONTENT mode for fragments
SELECT XMLPARSE(CONTENT '<item>apple</item><item>banana</item>');

-- FIX 2: Wrap in a root element
SELECT XMLPARSE(DOCUMENT '<items><item>apple</item><item>banana</item></items>');

2. Malformed XML (Not Well-Formed)

Missing closing tags, improper nesting, or unescaped special characters (&, <, >) will cause this error.

-- ERROR: missing closing tag
SELECT '<name>John Doe'::xml;

-- ERROR: unescaped ampersand
SELECT '<price>10 & 20</price>'::xml;

-- FIX: Escape special characters properly
SELECT '<price>10 &amp; 20</price>'::xml;

-- FIX: Ensure proper tag closure
SELECT '<name>John Doe</name>'::xml;

3. Unvalidated External Data

Data arriving from APIs, file imports, or legacy systems may look like XML but contain HTML tags, encoding issues, or plain text mixed in.

-- Safe parsing function to avoid transaction rollbacks
CREATE OR REPLACE FUNCTION safe_parse_xml(p_input TEXT)
RETURNS XML AS $$
BEGIN
    RETURN p_input::xml;
EXCEPTION
    WHEN SQLSTATE '2200L' THEN
        RAISE WARNING 'Invalid XML document: %', LEFT(p_input, 200);
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Usage: returns NULL instead of raising an error
SELECT safe_parse_xml('<valid><node>ok</node></valid>');   -- returns xml
SELECT safe_parse_xml('<broken>no closing tag');           -- returns NULL

Quick Fix Solutions

-- Check if a value is a valid XML document before inserting
DO $$
DECLARE
    v_input TEXT := '<root><item>test</item></root>';
    v_xml   XML;
BEGIN
    BEGIN
        v_xml := v_input::xml;
        RAISE NOTICE 'Valid XML: %', v_xml;
    EXCEPTION
        WHEN SQLSTATE '2200L' THEN
            RAISE WARNING 'Skipping invalid XML document.';
    END;
END;
$$;

-- Safe xpath usage with null guard
SELECT
    id,
    (xpath('//title/text()', data))[1]::TEXT AS title
FROM documents
WHERE data IS NOT NULL;

Prevention Tips

1. Validate XML at the application layer before sending to PostgreSQL.
Use libraries like Python's lxml, Java's DocumentBuilder, or Node's fast-xml-parser to validate XML before it reaches the database. Catching errors early avoids costly transaction rollbacks.

2. Standardize on CONTENT vs DOCUMENT mode across your codebase.
If your data can include fragments (multiple root nodes), always use XMLPARSE(CONTENT ...). Reserve XMLPARSE(DOCUMENT ...) only when a single root element is guaranteed. Document this decision in your team's coding standards to avoid inconsistency.

-- Recommended safe insert pattern
INSERT INTO xml_store (payload)
VALUES (XMLPARSE(CONTENT :user_input_text));

Related Errors

Code	Name
`2200M`	invalid XML content
`2200N`	invalid XML name
`22000`	data exception (parent category)

DEV Community: umzzil nng

PostgreSQL 22031 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22031: Invalid Argument for SQL JSON Datetime Function

Top 3 Causes

1. Non-ISO 8601 Date Strings in JSON Data

2. Mismatched Template Format String (PostgreSQL 15+)

3. Non-String or Unexpected Type Values

Quick Fix Solutions

Prevention Tips

Related Errors

Oracle ORA-00913 Error: Causes and Solutions Complete Guide

ORA-00913: Too Many Values — Causes, Fixes & Prevention

Top 3 Causes

1. Mismatch in INSERT ... VALUES Statement

2. Subquery Returns Too Many Columns

3. INSERT INTO ... SELECT Column Count Mismatch

Quick Fix Checklist

Prevention Tips

Related Errors

PostgreSQL 22030 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22030: duplicate json object key value

Top 3 Causes

1. Passing Duplicate Keys to jsonb_build_object()

2. Inserting External JSON Strings with Duplicate Keys into jsonb Columns

3. Duplicate Keys in json_object_agg() Aggregation

Quick Fix Solutions

Prevention Tips

Related Errors

Oracle ORA-00911 Error: Causes and Solutions Complete Guide

ORA-00911: Invalid Character — Causes, Fixes & Prevention

Top 3 Causes & SQL Examples

1. Trailing Semicolon in Application Code

2. Invisible or Non-ASCII Characters in SQL

3. Full-Width or Invalid Special Characters

Quick Fix Solutions

Prevention Tips

Related Oracle Errors

Oracle ORA-00910 Error: Causes and Solutions Complete Guide

ORA-00910: Specified Length Too Long for Its Datatype

Top 3 Causes

1. VARCHAR2 or CHAR Length Exceeds Oracle's Limit

2. NVARCHAR2 or NCHAR Exceeds Character Limit

3. Incorrect Length in PL/SQL Variable Declaration

Quick Fix Summary

Prevention Tips

Related Errors

PostgreSQL 2200S Error: Causes and Solutions Complete Guide

PostgreSQL Error 2200S: invalid xml comment

Top 3 Causes

1. Double Hyphens (--) Inside an XML Comment

2. Malformed Comment Closing Tag

3. Unsanitized User Input Injected into XML Comments

Quick Fix Solutions

Prevention Tips

Related Errors

Oracle ORA-00909 Error: Causes and Solutions Complete Guide

ORA-00909: Invalid Number of Arguments — Causes, Fixes, and Prevention

Top 3 Causes

1. Wrong Argument Count for a Built-in Function

2. Calling a User-Defined Function After a Signature Change

3. Dynamic SQL Building Incorrect Function Calls at Runtime

Quick Fix Checklist

Prevention Tips

Related Errors

PostgreSQL 2200N Error: Causes and Solutions Complete Guide

PostgreSQL Error 2200N: invalid xml content

Top 3 Causes & SQL Examples

1. Mismatched or Improperly Nested Tags

2. Unescaped Special Characters

3. Multiple Root Elements or Missing Root

Quick Fix Solutions

Prevention Tips

Related Errors

Oracle ORA-00907 Error: Causes and Solutions Complete Guide

ORA-00907: Missing Right Parenthesis — Causes, Fixes & Prevention

Top 3 Causes

1. Missing Parenthesis in DDL Statements

2. Unclosed Subquery or Inline View

3. Syntax Errors in CASE, IN, or Nested Functions

Quick Fix Checklist

1. Passing Duplicate Keys to `jsonb_build_object()`

2. Inserting External JSON Strings with Duplicate Keys into `jsonb` Columns

3. Duplicate Keys in `json_object_agg()` Aggregation

1. Double Hyphens (`--`) Inside an XML Comment