Postgresql or Python: How I can loop table records and create sequence number?-CodePudding

I have a task regarding creating sequence numbers on existing records. This table already created and have a ton of records and I created a new field as "UniqueId".

Let's see my table below:-

FieldA   FieldB  UniqueId
Zack     928334  
Yassir   829384  
Yassir   829384  
Zack     928334  
Zack     928334  
Richard  723346

What I need is PostgreSQL or Python can create a number started like "0501" on the first record if the same record found a new number but a sequence will be "0502" and so on.

FieldA   FieldB  UniqueId
Zack     928334  0501
Yassir   829384  0501
Yassir   829384  0502
Zack     928334  0502
Zack     928334  0503
Richard  723346  0501

I still don't have any idea regarding this matter and hope those who are already across this problem can help me to solve it.

I promptly thanks you very much in advance.

Thank you.

CodePudding user response：

You can get what you ask for with a relative simple trigger. (see demo)

create or replace function gen_unique_id()
  returns trigger 
 language plpgsql
as $$
begin 
    select coalesce( max(uniqueid) 1, 501)  
      into new.uniqueid
      from test 
     where fielda = new.fielda
       and fieldb = new.fieldb; 
   
    return new;
end; 
$$;

create trigger unique_id_biur
        before insert or update
            on test
          for each row
           execute function gen_unique_id();

CAUTION:

This is value assignment of max 1. In a multi-user environment virtually guarantees at some point to generate a duplicate. You need to handle the race conditions it enables.
You need to figure out what happens when fielda or fieldb is updated to a a set of values that do not exist or a gap is left in the old values. Also what happens when a row is deleted.

And there could be other ramifications.