CODEWITHSUNDEEP
sqlpostgresqlsequence
Sekhar Dutta
Sekhar Dutta

Reputation: 109

How to create sequence based on other column value

I have below data table where I wants to create a unique id for data_id which will be unique per group_id (group_id and data_id should be unique) and for each group data_id should start from 1. How to create a DB sequence to achieve this ? Or any other better approach to achieve this ?

group_id  data_id 
UUID-1     1  
UUID-1     2
UUID-1     3
UUID-1     4
UUID-2     1
UUID-2     2

Upvotes: 0

Views: 1692

Answers (2)

Abelisto
Abelisto

Reputation: 15614

create function tgf_mytable_bi()
    returns trigger
    language plpgsql
as $f$
declare
    seq_name text;
begin
    seq_name := 'seq_'||new.group_id;
    -- Check is sequence already exists
    if (select count(*) = 0 from pg_class where relkind = 'S' and relname = seq_name) then
        -- New group detected
        -- Create new sequence for it
        execute format('create sequence %I', seq_name);
        -- Sequence exists, get next value from it
    end if;
    -- Generate next value for group
    new.data_id := nextval(seq_name);
end $f$;

create trigger trg_mytable_bi
    before insert on mytable
    for each row
    execute procedure tgf_mytable_bi();

Upvotes: 0

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

DB Sequence will not help in this case. You can use trigger with some caveats:

Try this:

Create trigger function like below:

create or replace function trig_fun() 
returns trigger AS
$$
begin

select coalesce(max(data_id),0)+1 into new.data_id from my_table where group_id=new.group_id;

return new;

end;
$$
language plpgsql

and attach above function on before insert event

create trigger trig_on_insert 
before insert on 
my_table
for each row
execute procedure trig_fun()

Limitations:

  1. If you delete the row of any group having max value for that group, it will reassign same number on next insert of same group.
  2. If you update any ID which is greater than others in that group then next value will be incremented from it.

Upvotes: 1

Related Questions