Styjun

I have a postgres database that I can access with PGAdmin III. I run a script to change a number stored in a text field and then add left zeros to file it to four characters. It's a time field that must be stored as text. I'd like to do it in a single run instead of two. Here's the first clause to add hours to the time field as text;

UPDATE timetable
SET eta = (
 CASE 
 WHEN (trips.starttime::int / 100) + (destination.zuluoffset * -1 ) < 24 THEN ((trips.starttime::int / 100) + (destination.zuluoffset * -1 )) * 100
 WHEN (trips.starttime::int / 100) + (destination.zuluoffset * -1 ) > 23 THEN ((trips.starttime::int / 100) + (destination.zuluoffset * -1 ) - 24) * 100
 END )
FROM 
 destination, 
 trips
WHERE 
 timetable.tripsid = trips.id;

This does fine and adds the desired number of hours while correcting for results of greater than 24 hours. However, this leave any times less than 1000 hours as three digits or even a single 0 for midnight. The field needs to be 4 characters.

So I run this as a second clause;

UPDATE timetable
SET eta = lpad(eta, 4, '0');

and this works also. But how can I add the lpad to the first Update clause? I tried putting the entire CASE statement in the lpad statement in place of eta like this;

SET eta = lpad((CASE statement here), 4, '0')

but I get this error;

ERROR: function lpad(numeric, integer, unknown) does not exist
LINE 3: SET eta = lpad((
 ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts. 

I've tried casting eta with ::int, ::text, and ::varchar, but that just return a sytax error.

Why are you using LPAD if the result of your CASE statement is numeric , use simply to_char :

 UPDATE timetable
 SET eta = to_char (
 CASE 
 WHEN (trips.starttime::int / 100) + (destination.zuluoffset * -1 ) < 24 THEN ((trips.starttime::int / 100) + (destination.zuluoffset * -1 )) * 100
 WHEN (trips.starttime::int / 100) + (destination.zuluoffset * -1 ) > 23 THEN ((trips.starttime::int / 100) + (destination.zuluoffset * -1 ) - 24) * 100
 END , 'FM0000')
FROM destination
INNER JOIN trips ON timetable.tripsid = trips.id;

should be LPAD(your_col::text, 4, '0')

 UPDATE timetable
 SET eta = LPAD ((
 CASE 
 WHEN (trips.starttime::int / 100) + (destination.zuluoffset * -1 ) < 24 THEN ((trips.starttime::int / 100) + (destination.zuluoffset * -1 )) * 100
 WHEN (trips.starttime::int / 100) + (destination.zuluoffset * -1 ) > 23 THEN ((trips.starttime::int / 100) + (destination.zuluoffset * -1 ) - 24) * 100
 END )::text, 4,'0')
 FROM destination
 INNER JOIN trips ON timetable.tripsid = trips.id;