Unexpected behavior from ltrim in postgres

select ltrim('Test Thing', 'Test ');

Expected: 'Thing'

Actual Result: 'hing'

Why does the 'T' in 'Test' match and trim the 'T' from 'Thing'?

The 2nd parameter is the list of characters to trim, not a string as such.

The manual about ltrim(string text [, characters text]):


Remove the longest string containing only characters from characters
(a space by default) from the start of string.

So this:

select ltrim('abc cab b ca foo', 'abc ');

... only leaves 'foo'.

To actually remove one leading string 'Test ':

SELECT regexp_replace('Test ThingTest ', '^Test ', '');

But to update a table, this is typically much more efficient:

UPDATE tbl
SET    col = right(col, -5)  --  right(col, length('Test ') * -1)
WHERE  col LIKE 'Test %';

Only touches relevant rows (important!), can use an index where appropriate (important if only few rows in a big table are affected) and the expression is cheaper, too. About that index:

• PostgreSQL LIKE query performance variations

StackExchange Database Administrators Q#236821, answer score: 4