by David Crowther
Question:
How do you replace Blank values in a PostGIS Table?
Answer:
If you view the attributes for a Table in PostGIS and see that there are spaces within the text attributes, you should think about removing these blank values. This is because those values may cause issues when you look at selecting from/querying your data or running update scripts.
In this example my lsoa_shrewsbury table has a number of records where the stward_nam field value has blank values at the start:
Using this SQL Update query below, we can now update those records to remove the blank values by using the Left Trim function. Here we don’t need to define what we are trimming from the left of the field, as it assumes we are trimming blank values.
The SQL format is:
Update schema.table set "column" = ltrim("column");
… so for our data this will be:
Update public.lsoa_shrewsbury set "stward_nam" = ltrim("stward_nam");
Once we have ran the update query, the prefixed spaces/blank values will now be removed from the stward_nam field:
We can make this more powerful by searching for a specific value to remove. For example we may have a DASH value on the end of each stward_nam value that we wish to remove:
We can now edit the original update script and define the value that we wish to trim, remembering that we need to trim from the Right this time.
The SQL format is:
Update schema.table set "column" = rtrim("column",’value‘);
… so for our data this will be:
Update public.lsoa_shrewsbury set "stward_nam" = rtrim("stward_nam",’-‘);
Once we run this update script notice that the DASH value has now been removed from the right of the stward_nam values:
You will find the Ltrim and Rtrim update queries a powerful method to clean your data as you find these types of issues.