On 25/02/15 15:42, Merlin Moncure wrote: > On Sat, Feb 21, 2015 at 5:33 AM, Arjen Nienhuis <a.g.nienhuis@xxxxxxxxx> wrote: >> Hi, >> >> On 19 Feb 2015 17:12, "brian" <brian@xxxxxxxxxxxxxxxx> 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. > > You could also use 3 64 bit bigints if that's easier. The other way > to do it is bytea. The other way to do it is to have semantically-meaningful columns rather than glomming them into this 192-bit integer, and a composite key on the lot - if the set truly is unique. -- Jeremy -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
