OID is auto-incrementing integer value, unique within a PostgreSQL database (not just a table) 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 |
PostgreSQL OID:
How to add OID | Specify WITH OIDS option in CREATE TABLE | |
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 | ![]() |
|
Use in Primary Key | ![]() |
|
Use in Foreign Key | ![]() |
|
Gaps | ![]() |
|
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
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"
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:
Note that SERIAL data type does not have these limitations.
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.