onur onur - 5 months ago 31
SQL Question

postgresql update row to fixed digit number

I am beginner in sql query and I am trying to update my rows like that:





My column's type is text and there are lots of columns so I cannot handle with

update table1 set col1 = 0001 where col1 = 1;

and so on..

This seems easy question but after research,I could not find a solution. all I need is something like

foreach row in col1
if((int)row>0 and < 10)
then row = "000" + row;

All texts are infact integer value but I have to keep them as text. Whats sql query of above code?



You can use the lpad() function:

update table1
   set col1 = lpad(col1, 4, '0')
where length(col1) < 4;

But the real question is: why are you storing numbers as text values? That is almost always a bad choice.