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.
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
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
INSERT queries, Keeping the logic out of the DBMS is bas, so so bad.