How to add a leading zero based on range of values in postgres within an integer column

Question

New to postgres and unsure how to accomplish the following. I have a table as follows:

create table if not exists my_table (
   id int GENERATED BY DEFAULT AS IDENTITY primary key,
   key              int default 0
)

What I am trying to do is take an integer value (my_key) and if it's >= 0 and < 10 then add a leading zero (0) to it and insert it into my_table.key

I have tried to_char(my_key::integer,'09')::integer where my_key = 0 and it doesn't insert 00 within the key column.

Any help would be great.

Answer 1

Leading zeros don't change the value of an integer, so this is a question about formatting numbers.

If you want to display the id column with leading zeros, you could do that like this:

SELECT to_char(id, '00'), key
FROM "table";

The format 00 formats the number as a two-digit string with leasing zeros. If id is greater than 99, the number cannot be formatted like this, and you will get ##.

See the documentation for details about to_char and the available formats.