How to add a leading zero when the length of the column is unknown?

Question

How can I add a leading zero to a varchar column in the table and I don't know the length of the column. If the column is not null, then I should add a leading zero.

Examples:

345 - output should be 0345
4567 - output should be 04567

I tried:

SELECT lpad(column1,WHAT TO SPECIFY HERE?, '0') 
from table_name;

I will run an update query after I get this.

Answer 1

You may be overthinking this. Use plain concatenation:

SELECT '0' || column1 AS padded_col1 FROM table_name;

If the column is NULL, nothing happens: concatenating anything to NULL returns NULL.

In particular, don't use concat(). You would get '0' for NULL columns, which you do not want.

If you also have empty strings (''), you may need to do more, depending on what you want.

And since you mentioned your plan to updated the table: Consider not doing this, you are adding noise, that could be added for display with the simple expression. A VIEW might come in handy for this.

If all your varchar values are in fact valid numbers, use an appropriate numeric data type instead and format for display with the same expression as above. The concatenation automatically produces a text result.

If circumstances should force your hand and you need to update anyway, consider this:

UPDATE table_name
SET    column1 = '0' || column1
WHERE  column1 IS DISTINCT FROM '0' || column1;

The added WHERE clause to avoid empty updates. Compare:

• How do I (or can I) SELECT DISTINCT on multiple columns?

Answer 2

try concat instead?..

SELECT concat(0::text,column1) from table_name;