OIDs

I l @ ve RuBoard

PostgreSQL makes use of object identifiers (OIDs) and temporary identifiers (TIDs) to correlate table rows with system tables and temporary index entries.

Every row inserted into a table in PostgreSQL has a unique OID associated with it. In fact, every table in PostgreSQL contains a hidden column named oid . For instance:

 SELECT * FROM authors;  Name              Title  --------------------------------------------------- Bill Smith        Cooking for 6 Billion  Sam Jones         Chicken Soup for the Publishers Soul  SELECT oid, * FROM authors;  Oid   Name              Title  ------------------------------------------------------------ 17887 Bill Smith        Cooking for 6 Billion  18758 Sam Jones         Chicken Soup for the Publishers Soul 

The key concept to understand with OIDs is that they are not sequential within a table. OIDs are issued for every row item in the entire database; they are not specifically constrained to one table. Therefore, any one table will never contain a sequential ordering of OIDs. The SERIAL data type or an autonumbering SEQUENCE is best suited for that type of application.

By default, PostgreSQL reserves the OIDs from 0 to 16384 for system-only use. Therefore, user table-rows will always be assigned an OID greater than this.

PostgreSQL also uses TIDs to make dynamic relations between rows of data and index entries.This value fluctuates and is only used for internal system purposes.

A common question is how to create an exact copy of a table, including the original OIDs.This is made possible by utilizing the OID data type provided by PostgreSQL.

For instance:

 CREATE TABLE new_authors       (orig_oid oid, name char(10), title char(30));  SELECT oid, name, title INTO new_authors FROM authors;  COPY new_authors TO '/tmp/newauth';  DELETE FROM new_authors;  COPY new_authors WITH OIDS FROM '/tmp/newauth'; 

I l @ ve RuBoard