PostgreSQL Error 22027: Trim Error — Causes, Fixes, and Prevention
PostgreSQL error code 22027 (trim_error) occurs when the TRIM(), BTRIM(), LTRIM(), or RTRIM() functions receive invalid or incompatible arguments during execution. This error commonly surfaces during data cleansing operations, ETL pipelines, or when user-supplied input is passed directly into SQL without proper validation. Understanding its root causes can save significant debugging time in production environments.
Top 3 Causes
1. Passing Invalid or Empty Trim Characters
Supplying an empty string or NULL as the trim character set leads to unexpected behavior or a direct 22027 error.
-- Problematic: empty string as trim character
SELECT TRIM('' FROM some_column) FROM some_table;
-- Fix: use COALESCE to handle NULLs and default BTRIM
SELECT BTRIM(COALESCE(some_column, '')) FROM some_table;
-- Fix: explicitly specify valid trim characters
SELECT BTRIM(some_column, ' tnr') FROM some_table;
-- Safe wrapper function
CREATE OR REPLACE FUNCTION safe_trim(p_input TEXT)
RETURNS TEXT AS $$
BEGIN
RETURN BTRIM(COALESCE(p_input, ''));
EXCEPTION
WHEN SQLSTATE '22027' THEN
RETURN p_input;
END;
$$ LANGUAGE plpgsql;
2. Data Type Mismatch
Passing non-text types such as integer, boolean, or bytea directly to TRIM without explicit casting causes the function to fail during implicit type conversion.
-- Problematic: applying TRIM to a non-text column
SELECT TRIM(integer_column) FROM some_table; -- ERROR 22027
-- Fix: explicit cast to TEXT before trimming
SELECT TRIM(integer_column::TEXT) FROM some_table;
-- Fix: handle bytea columns
SELECT TRIM(convert_from(bytea_column, 'UTF8')) FROM some_table;
-- Check column types before applying TRIM
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'your_table'
AND table_schema = 'public';
-- Safe type-aware TRIM using CASE
SELECT
CASE
WHEN pg_typeof(col)::TEXT IN ('text', 'character varying', 'char')
THEN TRIM(col::TEXT)
ELSE col::TEXT
END AS trimmed_col
FROM your_table;
3. Unvalidated Dynamic SQL or ORM-Generated Queries
When application frameworks or ORMs dynamically construct SQL with unvalidated user inputs, TRIM arguments can be malformed or entirely missing, causing runtime 22027 errors.
-- Safe dynamic TRIM with input validation in PL/pgSQL
DO $$
DECLARE
v_raw_input TEXT := ' user input value ';
v_clean TEXT;
BEGIN
IF v_raw_input IS NOT NULL AND LENGTH(v_raw_input) > 0 THEN
v_clean := BTRIM(v_raw_input);
RAISE NOTICE 'Cleaned value: [%]', v_clean;
END IF;
END;
$$;
-- Bulk data cleansing with error safety
UPDATE users
SET username = BTRIM(username)
WHERE username IS NOT NULL
AND username <> BTRIM(username);
Quick Fix Solutions
If you encounter error 22027 in production, apply these immediate remedies:
-- 1. Wrap TRIM calls with exception handling
CREATE OR REPLACE FUNCTION robust_trim(p_value ANYELEMENT)
RETURNS TEXT AS $$
BEGIN
RETURN BTRIM(p_value::TEXT);
EXCEPTION
WHEN SQLSTATE '22027' THEN
RAISE WARNING 'trim_error on value: %', p_value;
RETURN p_value::TEXT;
END;
$$ LANGUAGE plpgsql;
-- 2. Validate and clean data before processing
SELECT robust_trim(col) FROM your_table WHERE col IS NOT NULL;
-- 3. Use regexp_replace as an alternative for edge cases
SELECT regexp_replace(some_column, '^s+|s+$', '', 'g')
FROM some_table;
Prevention Tips
1. Always cast to TEXT before trimming
Never assume a column is already in text format. Add explicit ::TEXT casts in any query that uses TRIM, especially in dynamically generated SQL.
2. Use auto-trim triggers for critical tables
CREATE OR REPLACE FUNCTION trg_auto_trim() RETURNS TRIGGER AS $$
BEGIN
NEW.name := BTRIM(COALESCE(NEW.name, ''));
NEW.email := BTRIM(LOWER(COALESCE(NEW.email, '')));
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER auto_trim_before_insert
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION trg_auto_trim();
These two habits alone will eliminate the vast majority of 22027 errors in production. Always validate inputs at both the application and database layers to build a robust defense against trim-related failures.
📖 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.
