Creating sequence for longer numeric values

Question

I need to create a database model that includes a field to store 20 digit unique values. Which will be used by many clients over an API.

The value must be 20 digit long, numeric and unique. Best approach seemed to create an auto increment big-int field, but big-int supports values up to 9223372036854775807 (19 digits). Using numeric(20,0) is useless since although numeric supports larger values, Postgres 8.4 do not let me create a sequence and raises error

CREATE SEQUENCE my_sequence_name INCREMENT BY 1 NO MINVALUE NO MAXVALUE START WITH 10000000000000000101;

ERROR: value "10000000000000000101" is out of range for type bigint

For data integrity, I must handle this within POstgresql. What is the efficient way to handle this?

I am using Postgresql 8.4 and Django 1.3

UPDATE: I guess it is better to make a clarification... I am trying to find a way to solve this within DBMS. So any non-database solutions will be avoided unless I had no other choice. Rest is the reason of my question:

I am making an API which communicates with a Bank for 3D secure operations. The bank API required a 20 digit unique numeric identifier for the transaction, so I decided to create an ID value that I can both use for the bank API and my API that will use that table etc.

Stating from 10000000000000000101 is because, bank API want it to be a valid 20 digit integer value, so It must be at least 10000000000000000000.

Solving this within the DBMS is best for performance and data integrity. An idea I thought was storing the value as an auto-increment big-integer in the database field, and manually parse it to string and make some make-up like:

bid = str(23434545) # value in the DB field
final_value = '1' + '0'* (19-len(bid)) + bid

which make final_value a 20 digit integer that starts with 1 and end with my auto-incremented value. and middle is filled with zeros.

But in case of dealing with both SELECT and INSERT queries, Keeping the logic out of the DBMS is bas, so so bad.

Answer 1

If you can live with losing some values to the maximum value, you could combine a sequence with a fixed offset to get the 20 digits. I would also define a check constraint on the table to to make sure that accidental inserts without using the default value insert the wrong value:

create sequence my_sequence_name;
create table foo 
(
   id numeric(20,0) default 10000000000000000001 + nextval('my_sequence_name'),
   constraint check_range check (id between 10000000000000000001 and 99999999999999999999)
);

That will give you a maximum generated value of 19223372036854775808 which doesn't use the complete range of allowed values, but it might be enough for your needs.

Answer 2

A simple solution would be to do the append on the server side like this:

INSERT INTO record(bid, ...) VALUES('1' || lpad(nextval('bid'), 19, '0'))