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
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:
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();
row_number()data_idalready seems to meet your requirements. You also define what you want a both a "unique id" and "unique id per group". Something either is or is not unique, so that is confusing.group_id. Is there a specific reason why you need your keys to be like this?