Hi,
On 19 Feb 2015 17:12, "brian" <brian@meadows.pair.com> wrote:
>
>
> Hi folks,
>
> I have a single-user application which is growing beyond the
> fixed-format data files in which it currently holds its data, I need a
> proper database as the backend. The front end is written using Lazarus
> and FreePascal under Linux, should anyone feel that makes a
> difference. The database will need to grow to around 250,000 records.
>
> My problem is with the data field which is the (unique) key. It's
> really a single 192-bit integer (it holds various bits of bitmapped
> data) which I currently hold as six 32-bit integers, but can convert
> if needed when transferring the data.
>
> How would you advise that I hold this field in a Postgres database,
> given the requirement for the whole thing to be a unique key? The
> first 64 bits change relatively infrequently, the last 128 bits will
> change with virtually every record. The last 128 bits will ALMOST be
> unique in themselves, but not quite. :(
>
> Thanks,
>
> Brian.
>
Postgres can use almost anything as a key so it probably depends on the library you use to access the database.
If it supports "composite primary keys" you can use the 6 ints as a key:
PRIMARY KEY (n1, n2, n3, n4, n5, n6)
The numeric type can hold 192-bit numbers. I think Lazarus supports this as well.
You could also use a surrogate key and define a UNIQUE constraint on the 6 ints or the 192-bit number.