PostgreSQL 2200F Error: Causes and Solutions Complete Guide

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('[e-mail]', '');

-- Fix: Use NULLIF to convert empty string to NULL
SELECT regexp_match('[e-mail]', 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('[e-mail]', '');
-- 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.