How to fix 22027: trim_error in PostgreSQL

PostgreSQLBEGINNERMEDIUMUpdated Dec 29, 2025· 113 views

This PostgreSQL error occurs when the TRIM() function receives invalid arguments or data types. The error happens when TRIM(), LTRIM(), or RTRIM() are called with incompatible parameters, typically when the character argument is invalid or when passing non-string data types that cannot be trimmed.

Quick fix

Fixes 22027

-- Standard SQL syntax
TRIM([LEADING | TRAILING | BOTH] [characters] FROM string)

-- PostgreSQL shorthand
TRIM(string)           -- removes whitespace from both ends
LTRIM(string)          -- removes leading whitespace
RTRIM(string)          -- removes trailing whitespace

-- With character specification
TRIM(BOTH 'x' FROM 'xxxhelloxxx')
LTRIM(string, chars)   -- remove leading chars
RTRIM(string, chars)   -- remove trailing chars
  1. Check your TRIM() function syntax
  2. Understand TRIM() function variants
  3. Cast data to string type before trimming
  4. Fix character argument issues
postgresql — error
-- Standard SQL syntax
TRIM([LEADING | TRAILING | BOTH] [characters] FROM string)
-- PostgreSQL shorthand
TRIM(string)           -- removes whitespace from both ends
LTRIM(string)          -- removes leading whitespace
RTRIM(string)          -- removes trailing whitespace
-- With character specification
TRIM(BOTH 'x' FROM 'xxxhelloxxx')
LTRIM(string, chars)   -- remove leading chars
RTRIM(string, chars)   -- remove trailing chars
22027: trim_error

What this error means

The TRIM() function in PostgreSQL is used to remove specified characters from the beginning and/or end of a string. The error "22027: trim_error" occurs when the function receives invalid arguments that prevent the trimming operation from completing. This is a data exception error (SQLSTATE class 22) that indicates invalid parameters for string trimming. TRIM() has several forms: 1. TRIM([LEADING | TRAILING | BOTH] [characters] FROM string) 2. TRIM(string) - removes whitespace from both ends 3. LTRIM(string, characters) - removes from left 4. RTRIM(string, characters) - removes from right The error typically happens when: 1. The character argument is invalid or multiple characters when only one is expected 2. Non-string data types are passed where strings are required 3. NULL values are not handled properly in the character argument 4. The character argument is an empty string 5. Data types don't support trimming operations

Typical symptoms

  • Query fails with SQLSTATE 22027 error
  • Error appears when using TRIM(), LTRIM(), or RTRIM() functions
  • String cleaning or formatting operations fail
  • Data validation or normalization queries fail
  • Error message includes 'trim_error'
  • ETL processes or data import scripts fail on data cleaning steps
  • Regular expressions or special processing using TRIM variants fail

Common causes

  1. Passing an empty string as the character argument
  2. Providing multiple characters when only one is expected
  3. Using non-string data types (integer, boolean, etc.) for trimming
  4. Attempting to trim NULL values without proper handling
  5. Using invalid or unsupported character encoding
  6. Passing numeric or other incompatible data types
  7. Using TRIM() on data types that don't support string operations
  8. Character argument contains invalid Unicode or special characters
  9. Confusion between TRIM() variants and their parameter requirements

How to fix "22027: trim_error"

1Check your TRIM() function syntax

Examine the TRIM() function call in your query. Common syntax forms:

sql
-- Standard SQL syntax
TRIM([LEADING | TRAILING | BOTH] [characters] FROM string)

-- PostgreSQL shorthand
TRIM(string)           -- removes whitespace from both ends
LTRIM(string)          -- removes leading whitespace
RTRIM(string)          -- removes trailing whitespace

-- With character specification
TRIM(BOTH 'x' FROM 'xxxhelloxxx')
LTRIM(string, chars)   -- remove leading chars
RTRIM(string, chars)   -- remove trailing chars

Verify that:
1. You are passing a valid string (not NULL without handling)
2. The character argument is a single character (if specified)
3. All arguments are of string or compatible types
4. You're using the correct TRIM variant for your use case

Example of problematic calls:

sql
-- This will fail: empty string as character
SELECT TRIM('' FROM 'hello');

-- This might fail: multiple characters
SELECT TRIM('ab' FROM 'aabbhellobbaa');

-- This will fail: non-string data type
SELECT TRIM(123);
2Understand TRIM() function variants

PostgreSQL has multiple TRIM functions with different behaviors:

1. TRIM(string) - Basic form:
 - Removes leading and trailing whitespace (spaces, tabs, newlines)
 - No character argument needed
 - Works with any string value

2. TRIM([LEADING | TRAILING | BOTH] [char] FROM string):
 - LEADING: removes from left only
 - TRAILING: removes from right only
 - BOTH: removes from both sides (default)
 - char: single character to remove (optional, defaults to space)

3. LTRIM(string) - Remove leading whitespace:

sql
SELECT LTRIM('  hello');  -- 'hello'

4. LTRIM(string, chars) - Remove leading characters:

sql
SELECT LTRIM('xxxhello', 'x');  -- 'hello'

5. RTRIM(string) - Remove trailing whitespace:

sql
SELECT RTRIM('hello  ');  -- 'hello'

6. RTRIM(string, chars) - Remove trailing characters:

sql
SELECT RTRIM('helloxxx', 'x');  -- 'hello'

Test with simple literal values:

sql
-- These are all valid
SELECT TRIM('  hello  ');                          -- 'hello'
SELECT TRIM(LEADING 'x' FROM 'xxxhello');         -- 'hello'
SELECT LTRIM('xxxhello', 'x');                    -- 'hello'
SELECT RTRIM('helloxxx', 'x');                    -- 'hello'

-- Check your actual data
SELECT
  your_column,
  LENGTH(your_column) as len,
  OCTET_LENGTH(your_column) as bytes
FROM your_table;
3Cast data to string type before trimming

If your data is not a string type, explicitly cast it first:

sql
-- Cast non-string types to text
SELECT TRIM(CAST(numeric_column AS TEXT))
FROM your_table;

-- Or use :: operator (PostgreSQL syntax)
SELECT TRIM(numeric_column::TEXT)
FROM your_table;

-- Examples with different types
SELECT TRIM(CAST(employee_id AS TEXT));  -- Convert integer
SELECT TRIM(CAST(price AS TEXT));        -- Convert numeric/decimal
SELECT TRIM(boolean_column::TEXT);       -- Convert boolean

-- For UUID or other special types
SELECT TRIM(CAST(uuid_column AS TEXT));

This is common when:
- Importing data from different sources
- Working with stored procedures that return mixed types
- Using columns that were incorrectly defined

4Fix character argument issues

The character argument must be a single character:

sql
-- WRONG: empty string or multiple characters
SELECT TRIM('' FROM 'hello');        -- Error
SELECT TRIM('ab' FROM 'hello');      -- Error

-- CORRECT: single character
SELECT TRIM('x' FROM 'xxxhelloxxx'); -- 'hello'
SELECT TRIM(LEADING 'a' FROM 'aaa');  -- ''
SELECT TRIM(TRAILING 'b' FROM 'abb'); -- 'a'

-- If you need to remove multiple different characters, use regexp
SELECT regexp_replace('xxxhelloyyy', '^x+|y+$', '', 'g');  -- 'hello'

-- Or use TRANSLATE() to remove multiple characters
SELECT TRANSLATE('xxxhelloxxx', 'xy', '');  -- 'hello'

Remember:
- Character argument is optional in TRIM(string) form
- If specified, it must be exactly one character
- Use regular expressions for complex trimming

5Handle NULL values properly

TRIM() returns NULL if the input string is NULL. Add NULL handling:

sql
-- Use COALESCE to provide defaults
SELECT TRIM(COALESCE(your_column, ''))
FROM your_table;

-- Or filter out NULL rows
SELECT TRIM(your_column)
FROM your_table
WHERE your_column IS NOT NULL;

-- For conditional trimming
SELECT
  CASE
    WHEN your_column IS NULL THEN NULL
    ELSE TRIM(your_column)
  END as trimmed_value
FROM your_table;

-- When trimming with character argument
SELECT TRIM(BOTH COALESCE(char_arg, ' ') FROM COALESCE(string_arg, ''))
FROM your_table;

-- Practical example: clean user input
SELECT
  TRIM(COALESCE(email, '')) as email,
  TRIM(COALESCE(first_name, '')) as first_name
FROM users;
6Use alternative string functions if needed

If TRIM() is causing persistent issues, consider alternatives:

1. Use built-in whitespace trimming for common cases:

sql
-- Simple whitespace removal
SELECT BTRIM(your_column)     -- Remove both sides
SELECT LTRIM(your_column)     -- Remove left only
SELECT RTRIM(your_column)     -- Remove right only

2. Use regexp_replace() for complex patterns:

sql
-- Remove multiple character types
SELECT regexp_replace(your_column, '^s+|s+$', '', 'g');

-- Remove specific characters
SELECT regexp_replace(your_column, '^[xyz]+|[xyz]+$', '', 'g');

3. Use TRANSLATE() to remove multiple characters:

sql
-- Remove all occurrences of specific characters
SELECT TRANSLATE(your_column, 'xyz', '');

4. Use string slicing for position-based trimming:

sql
-- Remove first/last N characters
SELECT SUBSTRING(your_column, 2);            -- Remove first char
SELECT SUBSTRING(your_column FROM 1 FOR LENGTH(your_column) - 1);  -- Remove last

5. Use STRIP() for specific edge cases:

sql
-- If working with arrays or composite types
SELECT (TRIM(elem))
FROM UNNEST(array_column) as elem;

Advanced notes

### PostgreSQL TRIM() Implementation Details

1. TRIM() Variants:
 - BTRIM() is PostgreSQL alias for TRIM(BOTH ... FROM ...)
 - All variants return NULL if input is NULL
 - Character argument defaults to space if omitted

2. Performance Considerations:
 - TRIM() can't use indexes on the result
 - For frequently trimmed columns, consider storing trimmed versions
 - Large string operations with multiple TRIM() calls can be slow
 - Use computed/generated columns for consistent trimming

3. Character Encoding:
 - TRIM() works with character positions, not bytes
 - In UTF-8, special characters are handled correctly
 - Character argument must be valid for the database encoding
 - Invalid UTF-8 sequences may cause trim_error

4. Whitespace Characters Matched by Basic TRIM():

sql
-- These are removed by TRIM() without arguments:
-- Space (0x20)
-- Tab (0x09)
-- Newline (0x0A)
-- Carriage return (0x0D)
-- Form feed (0x0C)
-- Vertical tab (0x0B)

5. Common Patterns:

sql
-- Normalize user input
UPDATE users
SET email = TRIM(LOWER(email)),
    name = TRIM(name)
WHERE email LIKE '%  %' OR name LIKE '%  %';

-- Clean imported data
SELECT
  TRIM(BOTH chr(34) FROM column1),  -- Remove double quotes
  TRIM(column2),                     -- Remove spaces
  TRIM(LEADING '0' FROM zipcode)    -- Remove leading zeros
FROM import_staging;

-- Combine with other string functions
SELECT
  TRIM(UPPER(SUBSTRING(description FROM 1 FOR 50))) || '...' as preview
FROM products;

### Error Code Hierarchy

- SQLSTATE Class: 22 (Data Exception)
- Subclass: 027 (Trim Error)
- Full code: 22027

This error is raised by PostgreSQL's TRIM family of functions when the arguments violate constraints for string trimming operations. The exact behavior may vary slightly between PostgreSQL versions.

### Debugging Tips

1. Check data types with pg_typeof():

sql
SELECT pg_typeof(your_column), your_column
FROM your_table
LIMIT 1;

2. Test with literal values first:

sql
-- Isolate the problematic TRIM call
SELECT TRIM('  hello  ');  -- Should work
SELECT TRIM('x' FROM 'xxxhelloxxx');  -- Should work

3. Check for problematic characters:

sql
-- Check for empty strings or unexpected content
SELECT your_column, LENGTH(your_column), OCTET_LENGTH(your_column)
FROM your_table
WHERE OCTET_LENGTH(your_column) = 0
  OR your_column LIKE '%' || CHR(9) || '%';

4. Test in isolation:

sql
-- Extract just the problematic row
SELECT * FROM your_table WHERE id = ?;

-- Try trimming with simple forms
SELECT TRIM(your_column);
SELECT LTRIM(your_column);
SELECT RTRIM(your_column);

-- Then add character argument
SELECT TRIM(LEADING 'x' FROM your_column);

Frequently asked questions

What does "22027" mean?
The TRIM() function in PostgreSQL is used to remove specified characters from the beginning and/or end of a string. The error "22027: trim_error" occurs when the function receives invalid arguments that prevent the trimming operation from completing. This is a data exception error (SQLSTATE class 22) that indicates invalid parameters for string trimming.
How do I fix "22027"?
This PostgreSQL error occurs when the TRIM() function receives invalid arguments or data types. The error happens when TRIM(), LTRIM(), or RTRIM() are called with incompatible parameters, typically when the character argument is invalid or when passing non-string data types that cannot be trimmed.
What causes "22027"?
Passing an empty string as the character argument Providing multiple characters when only one is expected Using non-string data types (integer, boolean, etc.) for trimming
Is the "22027" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading