From: Aaron Bono <postgresql@aranya.com>
To: David Clarke <pigwin32@gmail.com>
Cc: operationsengineer1@yahoo.com <operationsengineer1@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Alternative to serial primary key
Date: Thu, 6 Jul 2006 16:43:48 -0500
Message-ID: <bf05e51c0607061443t2eb0cbbfl569b7b25c74166d7@mail.gmail.com> (raw)
In-Reply-To: <12b7ac1e0607061330o34adbd6ds9bd624d3889c7fe7@mail.gmail.com>
References: <bf05e51c0607060902vdd8d993u470c128abf127d3d@mail.gmail.com>
<20060706183920.46008.qmail@web33314.mail.mud.yahoo.com>
<12b7ac1e0607061330o34adbd6ds9bd624d3889c7fe7@mail.gmail.com>
On 7/6/06, David Clarke <pigwin32@gmail.com> wrote:
>
>
> To recap, yes there is only a single column, yes it is varchar. I need
> to do a lookup on the address column which is unique and use it as a
> foreign key in other tables. Using a serial id would obviously work
> and has been recommended. But having a hash function over the address
> column as the primary key means I can always regenerate my primary key
> from the data which is impossible with a serial key. I believe the
> risk of collision using md5 is effectively zero on this data and I can
> put a unique index over it.
So if you have:
addresses
address_id bigserial (pk),
address
person
person_id bigserial (pk),
first_name,
last_name,
address_id
you can do something like
INSERT INTO person (
address_id
)
SELECT
'Joe',
'Blow',
address_id
FROM addresses
WHERE addresses.address = ?;
No regeneration of PK necessary. If you index addresses.address the insert
should run quickly, right?
-Aaron Bono
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@aranya.com, pigwin32@gmail.com, operationsengineer1@yahoo.com
Subject: Re: Alternative to serial primary key
In-Reply-To: <bf05e51c0607061443t2eb0cbbfl569b7b25c74166d7@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox