0

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.

nonothingnull
  • 37
  • 8

1 Answers1

1

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:

  1. 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.
  2. 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.

Belayer
  • 13,578
  • 2
  • 11
  • 22
  • Hello @Belayer, thank you for the answer. I am not familiar with functions and triggers on PostgreSQL. How do I call this thing from my flask python? do I need to create it on the PostgreSQL server first? – nonothingnull Dec 25 '22 at 18:29
  • You do not call a trigger. You just write the trigger function and define the trigger on the server. The function is called automatically when the appropriate DML (insert, update, delete) is issued. See documentation for [Trigger Functions](https://www.postgresql.org/docs/14/plpgsql-trigger.html) and [Create Trigger](https://www.postgresql.org/docs/14/sql-createtrigger.html). – Belayer Dec 25 '22 at 18:51
  • ok. let me try first then I will update the result – nonothingnull Dec 25 '22 at 18:57