PostgreSQL raises ERROR: trim_error with SQLSTATE 22027 when a call to a TRIM, BTRIM, LTRIM, or RTRIM function receives an invalid argument — most commonly a multi-character or empty trim character string in a context where exactly one character is expected. The condition name is trim_error and it belongs to the Class 22 — Data Exception error family.

What This Error Means

SQLSTATE 22027 is part of PostgreSQL's Class 22 data exception errors, which cover violations of data type rules and value constraints during expression evaluation. The error is raised at query execution time, not at parse or plan time, meaning the query structure is syntactically valid but the runtime value supplied to the trim function does not meet the function's requirements.

The standard SQL TRIM function accepts a single trim character. PostgreSQL's SQL-standard TRIM(LEADING 'x' FROM col) syntax requires that the trim character argument be a single character. When a string of length zero or length greater than one is provided, PostgreSQL raises SQLSTATE 22027. The native PostgreSQL functions BTRIM, LTRIM, and RTRIM accept a set of characters (any character in the string is trimmed), so they do not raise 22027 in the same way — the error surfaces specifically from the SQL-standard TRIM syntax with the FROM keyword.

After this error is raised, the current statement is aborted. If the statement was part of an explicit transaction, the transaction enters an error state and must be rolled back before any further work can proceed. The database connection itself remains intact.

Common Causes

Passing a multi-character string to SQL-standard TRIM. The SQL-standard syntax TRIM(LEADING 'ab' FROM col) expects exactly one character. Providing 'ab' (two characters) triggers the error.
Passing an empty string as the trim character. TRIM(BOTH '' FROM col) passes an empty string where a single character is required, which also raises 22027.
Dynamic values resolved to unexpected lengths. When the trim character comes from a variable, parameter, or subquery result rather than a literal, its length at runtime may differ from what was intended — for example, a parameter that could be NULL-safe-coalesced to an empty string.
Incorrect use of the SQL-standard TRIM syntax vs. PostgreSQL-native functions. Developers familiar with BTRIM(col, 'abc') (which accepts a character set, not a single character) may mistakenly use the equivalent SQL-standard form TRIM(BOTH 'abc' FROM col), which enforces the single-character restriction.

How to Fix `trim_error`

Use a single character with SQL-standard TRIM. Ensure the trim character argument is exactly one character long:

-- Correct: single character
SELECT TRIM(LEADING ' ' FROM col) FROM t;

-- Incorrect: multi-character string raises 22027
SELECT TRIM(LEADING '  ' FROM col) FROM t;

Switch to BTRIM/LTRIM/RTRIM when trimming a set of characters. If you want to strip any character from a given set, use the native PostgreSQL functions instead of the SQL-standard syntax:
```
-- Trims any of the characters '.', ',', or '!' from both ends
SELECT BTRIM(col, '.,!') FROM t;

-- Equivalent using LTRIM/RTRIM
SELECT LTRIM(RTRIM(col, '.,!'), '.,!') FROM t;
```

Guard against dynamic empty or multi-character values. When the trim character is supplied at runtime, validate or normalize it before use:

-- Only trim when the parameter is exactly one character
SELECT
  CASE
    WHEN LENGTH(:trim_char) = 1 THEN TRIM(BOTH :trim_char FROM col)
    ELSE col
  END
FROM t;

Check application code building dynamic SQL. If SQL is constructed in application code, assert that any variable substituted as a trim character has LENGTH(x) = 1 before executing the query.

Additional Information

SQLSTATE 22027 is defined in the SQL standard and is part of PostgreSQL's Class 22 (Data Exception) group, which includes related codes such as 22001 (string_data_right_truncation), 22003 (numeric_value_out_of_range), and 22019 (invalid_escape_character).
The error has been present in PostgreSQL since at least version 8.x and behaves consistently across all modern PostgreSQL versions (12 through 17).
Most PostgreSQL client drivers (libpq, psycopg2, psycopg3, asyncpg, JDBC) surface this as a generic DatabaseError or ProgrammingError with the SQLSTATE code in the exception details. Check e.pgcode (Python drivers) or getSQLState() (JDBC) to detect it programmatically.
ORMs such as SQLAlchemy, Django ORM, and ActiveRecord do not typically generate TRIM ... FROM ... SQL-standard syntax in their query builders; this error is more common in raw SQL, stored procedures, or hand-written queries.
Because this is a statement-level error, there is no partial data change to roll back — trim functions are read-only expressions that do not modify data.

Frequently Asked Questions

Why does BTRIM(col, 'abc') work but TRIM(BOTH 'abc' FROM col) raises an error?

BTRIM, LTRIM, and RTRIM accept a character set — every character in the provided string is individually considered a candidate for trimming. The SQL-standard TRIM ... FROM ... syntax treats its argument as a single trim character, so anything other than a one-character string is invalid. They look similar but follow different rules.

Does this error abort my entire transaction?

Only the current statement is aborted automatically. However, if the statement ran inside an explicit BEGIN / COMMIT block, the transaction is put into an error state and PostgreSQL will reject any further commands (with ERROR: current transaction is aborted) until you issue a ROLLBACK or ROLLBACK TO SAVEPOINT.

Can I catch SQLSTATE 22027 inside a PL/pgSQL function?

Yes. Use an EXCEPTION block and match on SQLSTATE '22027' or the condition name trim_error:

BEGIN
  result := TRIM(LEADING trim_char FROM input_value);
EXCEPTION
  WHEN trim_error THEN
    result := input_value; -- fall back to unmodified value
END;

Is there a way to trim more than one character using SQL-standard syntax?

No — the SQL standard allows only a single trim character in TRIM([LEADING|TRAILING|BOTH] char FROM string). To trim a set of characters, use PostgreSQL's BTRIM, LTRIM, or RTRIM functions, or apply REGEXP_REPLACE for more complex patterns.

PostgreSQL Trim Error (SQLSTATE 22027)

What This Error Means

Common Causes

How to Fix trim_error

Additional Information

Frequently Asked Questions

How to Fix `trim_error`