I have been using SERIAL a great deal when creating table primary keys.
The type of integer that this creates is an INT4, but I noticed that there is a larger INT8 available.
Would it be advisable to create a sequence manually and use the INT8 if the table size might be expected to grow beyond the 2 billion limit imposed by a INT4 (INTEGER) field type?
Unique BLOB's
I want to allow users to put blob's into a database. But I would like the database to recognise when a user is tying to insert a file that is identical to an existing file, as opposed to duplicating the blob. Either way I have to record the oid of the blob in a table so I can "locate" it later. I call this table blob_oid.
My plan is:
Add a field ( blob_md5 VARCHAR(32) ) to the blob_oid table and make a UNIQUE INDEX on this field to prevent duplicate values.
Before adding any blobs do a md5sum of the file and try inserting this value into blob_md5. If the file already exists we should get an error because it would have the same md5sum, otherwise upload the blob and update the blob_oid record created in the previous step.
I think this would work, but perhaps there is a better way?
