PostgreSQL 2200S Error: Causes and Solutions Complete Guide

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

---

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