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


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.

  • Are you sure the values don't need to be unpredictable (random, or at least pseudo-random)? Apr 20, 2013 at 10:59
  • @CraigRinger yes, I am sure, in fact, they told me it is better to be a sequence value.
    – Mp0int
    Apr 20, 2013 at 11:20
  • 1
    Is it ok to have gaps in the sequence? Apr 20, 2013 at 13:56
  • @MikeSherrill'Catcall' preffered is no, but it is ok if there occurs some.
    – Mp0int
    Apr 20, 2013 at 13:57

2 Answers 2


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.

  • 1
    or check (id between 10000000000000000001 and 19223372036854775808)? Apr 20, 2013 at 14:15
  • @JackDouglas: would make sense as well, yes. 99999999999999999999 "complies" with the actual business requirement, that's why I used that.
    – user1822
    Apr 20, 2013 at 16:19

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'))
  • What will be the type of bid in here?
    – Mp0int
    Apr 20, 2013 at 10:40
  • 1
    @FallenAngel text. Which is quite reasonable if the API specifies "20 digits" rather than "a value between ... and ..." like a sensibly written one would. Apr 20, 2013 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.