Quick little blog post to celebrate spending almost all of today working out how to keep a leading zero when you extract the DAY from a date field in PostgreSQL. You see normally you can do something like this...
SELECT EXTRACT(DAY FROM date) FROM yourtable
Which is fine. It will give you a value between 1 and 31. However I wanted the values below 10, to be returned as 01, 02, 03 etc. Alot of messing around has finally led to me finding the 'lpad' function. What that does is tell your query how many characters the result should be, and then pad it with whatever you want. So if I tell the query my result should be 10 characters long, with padding X, then when it grabs the value '1' it actually turns it into 'XXXXXXXXX1' (note the 9 X's). So using this, I told it that the result should be two characters long, and that it should pad with '0's. End result below.... awesome...especially pleased as I figured it out all by myself... haha.
UPDATE camdencrimes2
SET camdengps_link = (EXTRACT(YEAR FROM date_of_incident) || '' || EXTRACT(MONTH FROM date_of_incident) || '' || (SELECT lpad (cast((EXTRACT(DAY from date_of_incident)) as varchar), 2, '0')) || '' || incident_id);
SELECT DISTINCT(camdengps_link) FROM camdencrimes2
EDIT: Have since found out that there is a MUCH easier way to do this using the to_char function and defining the data output as 'DDMMYYYY'. I thought something like this would exist but couldn't find it until someone told me.
