Re: Use of OIDS as primary keys

On Sat, May 11, 2002 at 10:56:00PM -0700, Alan Wayne wrote:
> Hi!
>
> I'm wondering how I can use the system generated OIDS
> as primary keys. Does postgre automatically fill in
> the system generated oid when a field of type oid is
> created as the primary key? And if so, how do I read
> it back on a record just created so I can use it as a
> secondary key in another table? And lastly, is it even
> a good idea to use the oid as keys at all when I'm
> going to eventually migrate the data from one machine
> to another?

I'm sure this is mentioned in the FAQ somewhere but in postgres every tuple
has an OID (except in 7.2 where you can specify that you don't want them for
certain tables). It's not output by default but you can show it using
"select *,oid from table;".

Cons of using it as primary key:
- It's not guarenteed to be unique
- Forget the -o switch on your dump and your DB is hosed
- Moving to another machine can become a pain

Pros of using it as a primary key:
- INSERT tells you the oid it just inserted

Just use a sequence/serial. It's far clearer and more reliable.

HTH,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Canada, Mexico, and Australia form the Axis of Nations That
> Are Actually Quite Nice But Secretly Have Nasty Thoughts About America