PostgreSQL Error 2200F: Zero Length Character String
PostgreSQL error 2200F: zero_length_character_string occurs when an empty string ('') is passed to a function or operator that requires at least one character in its input. Unlike a NULL value, an empty string is a defined value with zero length, and certain PostgreSQL internals — particularly regex engines and strict type validators — explicitly reject it. This error is especially common in applications that pass user input directly to SQL functions without prior validation.
Top 3 Causes
1. Passing Empty String to Regex Functions
Functions like regexp_match(), regexp_replace(), and regexp_split_to_table() require a valid, non-empty pattern string.
-- Triggers 2200F error
SELECT regexp_match('[email protected]', '');
-- Fix: Use NULLIF to convert empty string to NULL
SELECT regexp_match('[email protected]', NULLIF('', ''));
-- Returns NULL safely instead of raising an error
-- Fix: Create a safe wrapper function
CREATE OR REPLACE FUNCTION safe_regexp_match(input_text TEXT, pattern TEXT)
RETURNS TEXT[] AS $$
BEGIN
IF pattern IS NULL OR pattern = '' THEN
RETURN NULL;
END IF;
RETURN regexp_match(input_text, pattern);
END;
$$ LANGUAGE plpgsql;
SELECT safe_regexp_match('[email protected]', '');
-- Returns NULL, no error
2. Using Empty Pattern with SIMILAR TO
The SIMILAR TO operator enforces stricter pattern validation than LIKE and will reject an empty string pattern.
-- Triggers 2200F error
SELECT * FROM products WHERE product_name SIMILAR TO '';
-- Fix: Guard with a CASE expression
SELECT * FROM products
WHERE
CASE
WHEN '' = '' THEN TRUE -- skip filter if pattern is empty
ELSE product_name SIMILAR TO ''
END;
-- Better fix: Use a PL/pgSQL function with conditional logic
CREATE OR REPLACE FUNCTION search_products(p_pattern TEXT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
IF p_pattern IS NULL OR p_pattern = '' THEN
RETURN QUERY SELECT product_id, product_name FROM products;
ELSE
RETURN QUERY
SELECT product_id, product_name FROM products
WHERE product_name SIMILAR TO p_pattern;
END IF;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM search_products(''); -- returns all rows safely
SELECT * FROM search_products('%apple%'); -- applies pattern filter
3. Domain or CHECK Constraint Rejecting Empty Strings
Custom domains or CHECK constraints that explicitly disallow empty strings will raise this error on INSERT or UPDATE.
-- Domain that rejects empty strings
CREATE DOMAIN non_empty_text AS TEXT
CHECK (VALUE IS NOT NULL AND length(trim(VALUE)) > 0);
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
full_name non_empty_text
);
-- Triggers error
INSERT INTO customers (full_name) VALUES ('');
-- Fix: Validate and trim before inserting
INSERT INTO customers (full_name)
SELECT NULLIF(trim(' '), '')::non_empty_text
WHERE NULLIF(trim(' '), '') IS NOT NULL;
-- Fix: Use a trigger to auto-validate input
CREATE OR REPLACE FUNCTION validate_customer()
RETURNS TRIGGER AS $$
BEGIN
NEW.full_name := trim(NEW.full_name);
IF NEW.full_name = '' THEN
RAISE EXCEPTION 'full_name cannot be empty (SQLSTATE 2200F)';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_validate_customer
BEFORE INSERT OR UPDATE ON customers
FOR EACH ROW EXECUTE FUNCTION validate_customer();
Quick Fix Solutions
The fastest universal fix is to sanitize inputs at the boundary using NULLIF and COALESCE:
-- Universal sanitization pattern
-- Convert empty string to NULL, then fall back to a default if needed
SELECT COALESCE(NULLIF(trim(:user_input), ''), 'default_value');
-- For dynamic queries, normalize all parameters upfront
DO $$
DECLARE
v_pattern TEXT := NULLIF(trim(''), ''); -- becomes NULL
BEGIN
IF v_pattern IS NOT NULL THEN
-- safe to use in regex or SIMILAR TO
END IF;
END;
$$;
Prevention Tips
1. Normalize inputs at the database boundary
Create a shared utility function and call it at every entry point to your database logic:
CREATE OR REPLACE FUNCTION require_non_empty(val TEXT, field TEXT DEFAULT 'input')
RETURNS TEXT AS $$
BEGIN
IF val IS NULL OR trim(val) = '' THEN
RAISE EXCEPTION 'Field "%" must not be empty.', field
USING ERRCODE = '2200F';
END IF;
RETURN trim(val);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
2. Treat empty strings as NULL in dynamic query builders
Always apply NULLIF(trim(value), '') to optional filter parameters before deciding whether to include them in a WHERE clause. This prevents empty patterns from reaching regex or SIMILAR TO operators and aligns with PostgreSQL's preference for NULL to represent "no value."
Related Errors
| Error Code | Name | Relation |
|---|---|---|
22001 | string_data_right_truncation | Opposite end of string length issues |
22P02 | invalid_text_representation | Invalid text cast, can co-occur |
23514 | check_violation | Raised by domain CHECK constraints |
22023 | invalid_parameter_value | Sometimes reported instead of 2200F |
📖 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.