How to fix 22027: trim_error in PostgreSQL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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