X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 8E4869F94F3 for ; Thu, 6 Jul 2006 18:43:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 55176-08 for ; Thu, 6 Jul 2006 18:43:51 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.185]) by postgresql.org (Postfix) with ESMTP id 6C3329FA0EC for ; Thu, 6 Jul 2006 18:43:50 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id x4so1307nfb for ; Thu, 06 Jul 2006 14:43:48 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=sYKs1jUhebp1QycPp0Yynv8NNflUCyuVIUGNNH9kq14+NCd3iCjIhKu2H0GCErdJZLaBLEglRM3TaCkGmQOn+l4WyU/hqVs6YQIdtTCJMYjZoEG9Df3c5s4xmfyqwANs7Ci6XKMg8lCaJo1Y88gOk8Q3zTxeVx2gj539iBvCm/k= Received: by 10.78.160.2 with SMTP id i2mr414908hue; Thu, 06 Jul 2006 14:43:48 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Thu, 6 Jul 2006 14:43:48 -0700 (PDT) Message-ID: Date: Thu, 6 Jul 2006 16:43:48 -0500 From: "Aaron Bono" To: "David Clarke" Subject: Re: Alternative to serial primary key Cc: "operationsengineer1@yahoo.com" , pgsql-sql@postgresql.org In-Reply-To: <12b7ac1e0607061330o34adbd6ds9bd624d3889c7fe7@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_100824_11948875.1152222228302" References: <20060706183920.46008.qmail@web33314.mail.mud.yahoo.com> <12b7ac1e0607061330o34adbd6ds9bd624d3889c7fe7@mail.gmail.com> X-Google-Sender-Auth: d55d55285368cb30 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/72 X-Sequence-Number: 25646 ------=_Part_100824_11948875.1152222228302 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/6/06, David Clarke 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 ------=_Part_100824_11948875.1152222228302 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

------=_Part_100824_11948875.1152222228302--