Skip site navigation (1) Skip section navigation (2)

Re: oids as primary keys?

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Cima <ruel(dot)cima(at)facinf(dot)uho(dot)edu(dot)cu>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: oids as primary keys?
Date: 2005-04-15 16:04:52
Message-ID: 20050415160452.GA83778@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
On Fri, Apr 15, 2005 at 03:42:40PM -0700, Cima wrote:
> 
> someone has drawn my attention to the fact that oids may not be such a good
> idea to set as a primary key in a table. i have designed a relativley large
> database and defined oids as primary keys. i would like your opinions or
> recomendations on this.

See "Object Identifier Types" in the "Data Types" chapter of the
documentation:

http://www.postgresql.org/docs/8.0/interactive/datatype-oid.html

"The oid type is currently implemented as an unsigned four-byte
integer.  Therefore, it is not large enough to provide database-wide
uniqueness in large databases, or even in large individual tables.
So, using a user-created table's OID column as a primary key is
discouraged.  OIDs are best used only for references to system
tables."

See also "What is an OID?  What is a TID?" in the FAQ:

http://www.postgresql.org/docs/faqs.FAQ.html#4.12

"OIDs are autotomatically assigned unique 4-byte integers that are
unique across the entire installation.  However, they overflow at
4 billion, and then the OIDs start being duplicated."

"To uniquely number columns in user tables, it is best to use SERIAL
rather than OIDs because SERIAL sequences are unique only within a
single table and are therefore less likely to overflow.  SERIAL8
is available for storing eight-byte sequence values."

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

In response to

pgsql-novice by date

Next:From: CimaDate: 2005-04-15 19:04:01
Subject: file oids
Previous:From: Frank BaxDate: 2005-04-15 16:00:30
Subject: Re: oids as primary keys?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group