This is an old revision of the document!


PostgreSQL - OID System Column - Table with OIDs (Identity, Autoincrement)

OID is auto-incrementing integer value, unique within a PostgreSQL database that can be automatically assigned to each row of a table created WITH OIDS option.

Although OID can be used as an identity (auto-increment) primary key column, it is recommended to use SERIAL data type instead. For more information, see Generating IDs in PostgreSQL.

Quick Example:

   -- Create a table with OID column
   CREATE TABLE aircraft
   (
      name VARCHAR(90)
   ) WITH OIDS;
 
   -- Insert a row
   INSERT INTO aircraft VALUES ('Boeing 747');
 
    -- Get generated OID
    SELECT oid, name FROM aircraft;

Query result:

oid name
16407 Boeing 747

OID (Object Identifier) Overview

PostgreSQL OID:

How to add OID Specify WITH OIDS option in CREATE TABLE
Default OID is not added by default Can be changed by default_with_oids server option
Range 32-bit unsigned integer - 232-1 (4 billion)
Cycle Starts with 1 after reaching the max value, so duplicates can appear
SELECT * FROM * does not include oid to the column list, but you can use SELECT *, oid FROM …
Uniqueness Across all tables until the max value is reached
Use in Primary Key
Use in Foreign Key
Gaps Since OID is shared among all tables
Explicit INSERT
Updating OIDs
Retrieve Last OID INSERT … RETURNING oid returns OID immediately after INSERT statement
GET DIAGNOSTICS varname = RESULT_OID a PL/pgSQL statement
pg_last_oid() a PHP function
Copy/Restore pg_dump preserves OIDs if -o option is used
INSERT INTO … SELECT loses OIDs (new OIDs are generated)
Typical Use of OIDs PostgreSQL system tables. Use in applications is discouraged
Alternatives SERIAL and BIGSERIAL data types

Version: PostgreSQL 9.1

PostgreSQL OID Details

If CREATE TABLE contains WITH OIDS option, PostgreSQL adds a system column oid to the table:

   CREATE TABLE airports
   (
      name VARCHAR(90)
   ) WITH OIDS;

OID values are shared among all tables within a database, so OID values may be not sequential within a single table:

   -- Let's insert into one table
   INSERT INTO airports VALUES ('O''Hare');   -- OID 16417 inserted
 
   -- Now insert into another table
   INSERT INTO aircraft VALUES ('Airbus A330');  -- OID 16418 inserted

OID column is not included into SELECT * FROM list, and you cannot explicitly insert or update the OID value:

   SELECT * FROM airports;
   -- "O'Hare"
 
   SELECT oid, * FROM airports;
   -- 16417, "O'Hare"
 
   INSERT INTO airports VALUES (10, 'Dulles');
   -- ERROR:  INSERT has more expressions than target columns
 
   UPDATE airports SET oid = 10 WHERE oid = 16417;
   -- ERROR:  cannot assign to system column "oid"

OID as Primary and Foreign Key

PostgreSQL allows you to use OID column in a primary or foreign key:

   -- Add a primary key to the parent table
   ALTER TABLE airports ADD PRIMARY KEY (oid);
 
   -- Create a child table and a foreign key
   CREATE TABLE hubs (id INT);
   ALTER TABLE hubs ADD FOREIGN KEY (id) REFERENCES airports (oid);
 
   -- Try to insert data
   INSERT INTO hubs VALUES (10);
   -- ERROR:  insert or update on table "hubs" violates foreign key constraint
 
   INSERT INTO hubs VALUES (16417);
   -- 1 row inserted

Restrictions:

If you want to use OIDs in a primary and foreign key, note the following restrictions:

  • You cannot modify OID values after they have been inserted (that is ok as updating of primary keys is discouraged)
  • If you copy a table with OIDs during a maintenance or reorganization procedures (INSERT INTO … SELECT) OIDs will be lost (new OID values will be generated)

Note that SERIAL data type does not have these limitations.

Retrieve Last Inserted OID

There are several reliable ways how to get the latest inserted OID value in the current session:

You can use RETURNING option of INSERT statement to return as a single row result set:

  -- Return OID as a single row result set
  INSERT INTO airports VALUES ('Dulles') RETURNING oid;
  -- Returns: 16429

Or into a variable in a PL/pgSQL function:

   CREATE OR REPLACE FUNCTION add_airport(name VARCHAR(90)) RETURNS INT AS $$
   DECLARE
     id INT = 0;
   BEGIN
      INSERT INTO airports VALUES (name) RETURNING oid INTO id;
      RETURN id;
   END;
   $$ LANGUAGE plpgsql;

Then when you call add_airport function, it returns OID:

   SELECT add_airport('Gatwick');
   -- Returns: 16433

Additionally, you can use GET DIAGNOSTICS statement to retrieve the last OID:

   CREATE OR REPLACE FUNCTION add_airport2(name VARCHAR(90)) RETURNS INT AS $$
   DECLARE
     id INT = 0;
   BEGIN
      INSERT INTO airports VALUES (name);
      GET DIAGNOSTICS id = RESULT_OID;
      RETURN id;
   END;
   $$ LANGUAGE plpgsql;

When you call add_airport2 function, it also returns OID:

   SELECT add_airport2('Heathrow');
   -- Returns: 16435

Note that you cannot create a user-defined function (UDF) to return OID using GET DIAGNOSTICS for any INSERT statement executed outside the UDF as GET DIAGNOSTICS works only for last SQL command.

So if INSERT is called outside the function, GET DIAGNOSTICS called inside the function cannot get the OID.

Resources