CODEWITHSUNDEEP
postgresqlincrement
user3365621
user3365621

Reputation: 47

PostgreSQL Increment varchar

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.

Upvotes: 1

Views: 3031

Answers (3)

felixbuenemann
felixbuenemann

Reputation: 639

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.

Upvotes: 2

Scoots
Scoots

Reputation: 3102

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();

Upvotes: 0

MoeAmeen Mayet
MoeAmeen Mayet

Reputation: 51

Try this...

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

Upvotes: -1

Related Questions