1

The particular postgres database table has 21 fields one of which is set for sequence as a primary key "repair_id". The other unique field called "repair_accession" is also a special unique TEXT in a format like "NH13-1".

I am looking for a reliable trigger function for generating unique number based on two letters, two digit year format, and an auto-increment number that resets to 1 annually on Jan 1st.

Example: for 2013 = NH13-1 up-to NH13-999999

and on January 1st 2014, it will reset itself and starts as NH14-1 up to NH14-999999

etc.

Your answer is much apprecaited!!

user2441588
  • 45
  • 1
  • 6

1 Answers1

0

You don't say what version you are using, but I'm assuming from the Tag that it's 9.2. IMO, you are gonna need a combination of things here:

  • a sequencer
  • a before insert trigger
  • a cron job (or similar event) to reset your sequencer at the start of the new year

Search and review documentation for:

  • Create Sequencer
  • nextval (to get the next value for generating the id)
  • setval (to reset via the cron job)
  • date_part (to get the year)

Basically, in your trigger, you will get the year via date_part and store it in a var (e.g. the_year), then:

NEW.repair_accession := 'NH'||the_year::text||'-'||nextval({{name of sequencer}})::text;

note: solution or code has not been tested. If you end up using this as the answer, I request that you update this answer with your actual code and remove this note.

One final suggest, if you are going to have to sort by this value, then I suggest you pad the value from the sequencer with zeros. For example:

NH13-0000100 (instead of HN13-100). This is probably not needed if you timestamp each record on insert.

Hope this helps.

David S
  • 12,967
  • 12
  • 55
  • 93
  • Thank you very much!! I will do that and update this. I am using version 9.2, you assumed right. – user2441588 Jun 18 '13 at 01:35
  • I found this link on stackoverflow and ended up using it Thanks for your suggestion though! http://stackoverflow.com/questions/16598959/custom-auto-increment-field-in-postgresql-invoice-order-no – user2441588 Jun 20 '13 at 01:53