Take the 2-minute tour ×

I have a database where I want to generate unique tokens of six numeric characters. This tokens should be generated each time an invoice number is inserted in the invoice_no column. This would mean that every time I do an insert and the column invoice_no is not null, the token is generated of the numerals. I want the token to be to generate as many tokens as possible without a token being repeated at any instance. Thanks

share|improve this question
Better suited for dba.stackexchange.com –  Burhan Khalid Aug 16 '12 at 6:04
Just a heads up on your requirements. If you say at most the token can be 6 numbers and they must be unique, you will run out of all possibilities with 999,999 inserts. Is this what you intend? –  Matt Aug 16 '12 at 6:17
add comment

closed as off topic by mu is too short, Burhan Khalid, jcolebrand, animuson, ypercube Aug 16 '12 at 8:50


1 Answer

You can create a sequence and use it to produce the tokens:


To set the current token to the next available value:

num_token := nextval('seq_token');

Your actual token has to be 6 digit long. If you use tokens beginning with zeros (eg. 002354), than the actual token has to be a text, which you can produce like

text_token := lpad(num_token::text, 6, '0');

If the tokens have to be numeric, than change MINVALUE to 100000.

(The assignments are in plpgsql. You can use these in a trigger or a stored procedure.)

share|improve this answer
add comment
