How to find and fix leading and trailing spaces in a Postgres column

Today I had a problem where a fix was applied a while ago to a service that used a DB table as a cache. The fix basically trimmed incoming data before inserting it into the DB.

The problem is that for historical data, the untrimmed entries are still there.

After a bit of investigation, selecting these is actually fairly easy with the help of Postgres' trim and length functions.

An example query to detect a leading or trailing space on the delivery table's address field is as follows:

select
    length(trim(d.address)) as "trimmed_length",
    length(d.address) as "untrimmed_length",
    replace(d.address, ' ','@') as "visual_of_space",
    d.address
from delivery d
    where length(trim(d.address)) < length(d.address);

And fixing it uses much of the same logic above but does an update:

update delivery
    set address = trim(address), updated = now()
where length(trim(d.address)) < length(d.address);