- Published on
How to find and fix leading and trailing spaces in a Postgres column
- Yair Mark
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'
An example query to detect a leading or trailing space on the
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);