Re: Use of OIDS as primary keys

Elaine Lindelef wrote:
>> On Sun, 2002-05-12 at 06:56, Alan Wayne wrote:
>> > I'm wondering how I can use the system generated OIDS
>> > as primary keys. Does postgre automatically fill in
>>
>> Don't do it!
>>
>> Oids are not saved in a dump unless you specifically ask for them;
>> you'll be in a right mess if you forget and then need to restore from
>> the dump. Then, oids aren't guaranteed to be unique. In any case, your
>> database design should not depend on an internal feature of the database
>> implementation.
>>
>> If there is no primary key in the data, create one by including a SERIAL
>> field (an integer with an associated next number generator) and use that
>> as the key.
>>
>> --
>> Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
>> Isle of Wight http://www.lfix.co.uk/oliver
>> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
>
> I think it would be valuable if a discussion of this were added to the
> FAQ somewhere. It took me a while to figure this out - in Oracle and
> mySQL I was used to making my own autoincrement columns, and at first
> usage it seemed that postgre was saving me from that bit of drudgery.
> ;^) Eventually I realized I needed my own columns, but it's not obvious
> and it's a bit of a pain to undo.

I'm also curious about the use of WITHOUT OIDS in the creation of
tables.
Somewhere I gleaned from the docs that OIDS aren't necessary, but they are
a good idea when there is no primary key. Thus, I've been adding "WITHOUT
OIDS" on any table that has a primary key.
Is there any drawback to this? I figure it's saving 4 bytes per record,
right? (it adds up when you have 100,000 records)
But I don't understand why OIDs are ever necessary. It seems like they
could be useful at times, but if I need something that works like a
primary key, I'll create a primary key.

I guess the ultimate question in all this is "Is there any ill effect from
using WITHOUT OIDS on a table that doesn't have a primary key?"

--
Bill Moran
Potential Technology
http://www.potentialtech.com