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('[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

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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();

Enter fullscreen mode Exit fullscreen mode

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;
$$;

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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.