Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Are you sure the values don't need to be unpredictable (random, or at least pseudo-random)? –  Craig Ringer Apr 20 '13 at 10:59
    
@CraigRinger yes, I am sure, in fact, they told me it is better to be a sequence value. –  FallenAngel Apr 20 '13 at 11:20
1  
Is it ok to have gaps in the sequence? –  Mike Sherrill 'Cat Recall' Apr 20 '13 at 13:56
    
@MikeSherrill'Catcall' preffered is no, but it is ok if there occurs some. –  FallenAngel Apr 20 '13 at 13:57
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
1  
or check (id between 10000000000000000001 and 19223372036854775808)? –  Jack Douglas Apr 20 '13 at 14:15
    
@JackDouglas: would make sense as well, yes. 99999999999999999999 "complies" with the actual business requirement, that's why I used that. –  a_horse_with_no_name Apr 20 '13 at 16:19
add comment

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'))
share|improve this answer
    
What will be the type of bid in here? –  FallenAngel Apr 20 '13 at 10:40
    
@FallenAngel text. Which is quite reasonable if the API specifies "20 digits" rather than "a value between ... and ..." like a sensibly written one would. –  Craig Ringer Apr 20 '13 at 11:34
    
@FallenAngel It's the SEQUENCE you'll draw values from. The other answer is good too. –  trygvis Apr 20 '13 at 12:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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