PostgreSQL Increment varchar

Question

Is there a way we can increment a column declared as varchar in PostgreSQL. IF I have values 0001,0002,0003... in my column how can proceed to add +1? I tried nextval but it not working.

CREATE TABLE tab1 (
matri VARCHAR(10) NOT NULL,
role VARCHAR(4) NOT NULL,
libelle VARCHAR(40),
util VARCHAR(10),
) 

CREATE TABLE tab2 (
idemploye SERIAL,
nomred VARCHAR(10),
matri VARCHAR(10),
)

Insert into tab1 ("matri", "role", "libelle", "util")
select tab2.matri,tab1.role,'MANAGER',E'MANAGER' from tab1,tab2
where tab2.matri IN (Select matri from tab2);

I want my second column "role" to be incremented with every insert.

The usual approach is to store that as an integer and format that number with leading zeros when displaying it. — user330315
– user330315, Commented Jul 19, 2018 at 14:38

felixbuenemann · Accepted Answer · 2018-07-19 16:19:47Z

2

Though the better solution would be to use an integer column and add the padding on display, this works:

CREATE SEQUENCE role_seq;
CREATE TABLE roles (role varchar(4) NOT NULL DEFAULT lpad(nextval('role_seq')::varchar, 4, '0'));
INSERT INTO roles VALUES (DEFAULT), (DEFAULT);
SELECT * FROM roles;
--  role
-- ------
--  0001
--  0002

Note that you will get gaps in the numbers if you delete rows or an insert transaction rolls back.

answered Jul 19, 2018 at 16:19

felixbuenemann

6391 gold badge5 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scoots · Accepted Answer · 2018-07-19 15:51:54Z

As a_horse_with_no_name has already stated, the ideal here is to store this as an int and format on display, but I assume your system is the way it is for a reason, so.

The way to do this would be, in the select subquery of your insert statement: select the highest existing value, cast to int, increment, cast back to varchar, run through lpad, and then just in case no data exists yet, coalesce with '0001'.

insert into tab1 ("matri", "role", "libelle", "util")
select
    tab2.matri,
    coalesce((select lpad((role::integer + 1)::varchar, 4, '0') from tab1 order by role desc limit 1), '0001'),
    'MANAGER',
    E'MANAGER'
from
    tab2
where
    tab2.matri IN (select matri from tab2);

An alternative approach would be to create a trigger to do this for you, so you don't need to do this manually everywhere you insert into the table:

-- Create trigger function
create or replace function tg_fn_tab1_varchar_increment(
) returns trigger as $$
declare
    _r varchar;
begin
    select into _r
        role
    from
        tab1
    order by
        role desc
    limit 1;

    if _r is null then
        NEW.role := '0001';
        return NEW;
    end if;

    NEW.role := lpad((_r::integer + 1)::varchar, 4, '0');

    return NEW;
end;
$$ language plpgsql volatile;

-- Attach trigger
create trigger tg_tab1_varchar_increment before insert on tab1
    for each row execute procedure tg_fn_tab1_varchar_increment();

MoeAmeen Mayet · Accepted Answer · 2018-07-19 14:09:50Z

-1

Try this...

select CONVERT(varchar,substring('0001',1,2)) +
   RIGHT(REPLICATE('0',5) + convert(varchar,(CONVERT(int, substring('0001',3,5)) + 1)) ,5);

answered Jul 19, 2018 at 14:09

MoeAmeen Mayet

5110 bronze badges

4 Comments

user3365621 Over a year ago

I have an error "the column "varchar" doesn't exist"

MoeAmeen Mayet Over a year ago

what actual values are you putting into the column role ... if it is just numbers you can make it an int instead of varchar and just include an auto-increment

user330315 Over a year ago

That is invalid standard SQL and invalid for Postgres

user3365621 Over a year ago

I convert it to an int then convert it to varchar like this : CAST (CAST(role AS INTEGER)+1 as text). Now is there a way I can prefix it with 000, and 00 if I get to double digits?

Collectives™ on Stack Overflow

PostgreSQL Increment varchar

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related