PostgreSQL 2200F Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 2200F: Zero Length Character String

PostgreSQL error 2200F (zero_length_character_string) is raised when an empty string ('') is passed to a function or operator that does not accept zero-length input. This most commonly occurs with regular expression functions, but can also surface during type casting or domain validation. It belongs to the SQLSTATE class 22 (Data Exception) family defined by the SQL standard.

Top 3 Causes

1. Passing an Empty Pattern to Regex Functions

Functions like regexp_replace(), regexp_match(), and regexp_split_to_table() reject empty string patterns at runtime.

-- Triggers ERROR 2200F
SELECT regexp_replace('hello world', '', 'X');

-- Also fails
SELECT regexp_match('some text', '');

-- Safe alternative using CASE guard
SELECT CASE
    WHEN pattern = '' OR pattern IS NULL THEN input_text
    ELSE regexp_replace(input_text, pattern, 'X')
END
FROM (VALUES ('hello world', '')) AS t(input_text, pattern);

2. Dynamic SQL with User-Supplied Empty Patterns

When search patterns come from user input or application parameters, an empty string can slip through without proper validation.

-- Dangerous: pattern variable could be empty
CREATE OR REPLACE FUNCTION search_items(pattern TEXT)
RETURNS SETOF TEXT AS $$
BEGIN
    RETURN QUERY
        SELECT title FROM articles WHERE title ~ pattern; -- FAILS if pattern = ''
END;
$$ LANGUAGE plpgsql;

-- Safe version with guard clause
CREATE OR REPLACE FUNCTION search_items_safe(pattern TEXT)
RETURNS SETOF TEXT AS $$
BEGIN
    IF pattern IS NULL OR length(trim(pattern)) = 0 THEN
        RETURN QUERY SELECT title FROM articles;
        RETURN;
    END IF;
    RETURN QUERY SELECT title FROM articles WHERE title ~* pattern;
EXCEPTION
    WHEN SQLSTATE '2200F' THEN
        RAISE WARNING 'Empty pattern detected in search_items_safe';
END;
$$ LANGUAGE plpgsql;

3. Empty String in Domain or Type Casting Contexts

Custom domain types with constraints or certain built-in type casts can trigger this error when an empty string is provided.

-- Domain that rejects empty strings
CREATE DOMAIN strict_text AS TEXT
    CHECK (VALUE IS NOT NULL AND length(trim(VALUE)) > 0);

-- This raises an error
INSERT INTO employees (name) VALUES (''::strict_text);

-- Safe insert using NULLIF to convert empty → NULL
INSERT INTO employees (name)
SELECT NULLIF(trim(user_input), '')
FROM staging;

Quick Fix Solutions

-- 1. Universal safe regex wrapper function
CREATE OR REPLACE FUNCTION safe_regexp_replace(
    src     TEXT,
    pat     TEXT,
    rep     TEXT,
    flags   TEXT DEFAULT 'g'
)
RETURNS TEXT AS $$
BEGIN
    IF pat IS NULL OR length(pat) = 0 THEN
        RETURN src;
    END IF;
    RETURN regexp_replace(src, pat, rep, flags);
EXCEPTION
    WHEN SQLSTATE '2200F' THEN
        RETURN src; -- Fail silently, return original
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 2. Inline guard with NULLIF
SELECT regexp_replace(col, NULLIF(:pattern, ''), 'replacement')
FROM my_table
WHERE :pattern IS NOT NULL AND length(:pattern) > 0;

-- 3. Catch it explicitly in a block
DO $$
BEGIN
    PERFORM regexp_replace('data', '', 'x');
EXCEPTION
    WHEN SQLSTATE '2200F' THEN
        RAISE NOTICE 'Caught zero_length_character_string - skipping operation';
END;
$$;

Prevention Tips

Validate at the database boundary. Add CHECK constraints or BEFORE triggers that reject empty strings before they reach any regex-processing function. This creates a reliable safety net independent of application logic.

-- Table-level constraint
ALTER TABLE search_queries
    ADD CONSTRAINT chk_non_empty_pattern
    CHECK (pattern IS NULL OR length(trim(pattern)) > 0);

Wrap all regex calls in helper functions. Centralizing regexp_* calls into validated wrapper functions means you fix the empty-string guard in one place and every caller benefits automatically. Always catch SQLSTATE '2200F' explicitly so unexpected empty inputs degrade gracefully rather than crashing your queries.

-- Reusable validation helper
CREATE OR REPLACE FUNCTION is_valid_regex_pattern(p TEXT)
RETURNS BOOLEAN LANGUAGE sql IMMUTABLE AS $$
    SELECT p IS NOT NULL AND length(trim(p)) > 0;
$$;

Related Errors

SQLSTATE	Name	Notes
`2201B`	`invalid_regular_expression`	Malformed regex syntax; often appears alongside 2200F
`22001`	`string_data_right_truncation`	Opposite end of the string-length spectrum
`22P02`	`invalid_text_representation`	Failed type cast; common when casting empty strings
`23514`	`check_violation`	Fires when domain/table CHECK blocks an empty string

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

DEV Community