Ran into an issue and was hoping to get some feedback. I have a postgres db table with columns for 'users', 'email' and '4 digit number' but when we migrated the data, it looks like any leading zero's were dropped from the '4 digit number' column. e.g. if User A had a '4 digit number' of 0123 it now shows as just 123.
Is there a way to query the table and identify all '4 digit number' less than 4 characters and then update the string and add the leading zero's? e.g. 123 should be 0123.
I don’t think that would be possible because the data in that column is set either value you put in it.
My thought here would be that your emigrate and set the column to be of a certain min length.
Or if you already know which users should have a leading 0 or 00 and the length must be at least 4 you could select all users put them in another table and then run a query to update only those users and then update the new values where user = user - hope that makes sense
Thanks for your reply! I got some inspiration and a little more digging and was able to run the query below and it worked! (by the way: can't have leading zeros if the column type is BIGINT)
update table set "4 digit number" = '0' || "4 digit number" where length("4 digit number") = 3;