how to add +1 to code ' 001' and to the code ' 009 ' in PostgreSQL?

Question

I'm trying to add +1 to code '001' but I get '2' and not ' 002 and +1 to code '009' but I get '10' and not '010'.

Table:

create table example
(
    code        varchar(7),
    row1        varchar(10),
    row2        varchar(7),


CONSTRAINT pk_code PRIMARY KEY (code)

);

Insert:

insert into example(code, row1, row2) 
values('001','x1', 'y1');

insert into example(code, row1, row2) 
values('009','x2', 'y2');

I try the next query:

select LPAD(cast(code as int)+1, 3, '0') from example;

Postgresql returns me:

HINT : No function matches the name and types of arguments. You may need to add explicit type conversion.

@juergen d solved it in mysql.

Answer 1

LPAD is expecting a string, so try:

select LPAD(cast(cast(code as int) + 1 as varchar), 3, '0') from example;