185

OIDs basically give you a built-in id for every row, contained in a system column (as opposed to a user-space column). That's handy for tables where you don't have a primary key, have duplicate rows, etc. For example, if you have a table with two identical rows, and you want to delete the oldest of the two, you could do that using the oid column.

OIDs are implemented using 4-byte unsigned integers. They are not unique–OID counter will wrap around at 2³²-1. OID are also used to identify data types (see /usr/include/postgresql/server/catalog/pg_type_d.h).

In my experience, the feature is generally unused in most postgres-backed applications (probably in part because they're non-standard), and their use is essentially deprecated:

In PostgreSQL 8.1 default_with_oids is off by default; in prior versions of PostgreSQL, it was on by default.

The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that require OIDs for a particular table should specify WITH OIDS when creating the table. This variable can be enabled for compatibility with old applications that do not follow this behavior.

6
  • 37
    oids are not guaranteed to be unique. From the docs: "In a large or long-lived database, it is possible for the counter to wrap around. Hence, it is bad practice to assume that OIDs are unique, unless you take steps to ensure that this is the case."
    – radiospiel
    Jul 24, 2013 at 15:43
  • 8
    The wrapping around also implies that you could not necessarily delete the older of two rows based only on their OID, as the one with the lower OID may have been a wrap-around.
    – Carl G
    Apr 18, 2014 at 17:30
  • 1
    OIDs are not globally unique, per comments above, nor were they in 2011 when this answer was written. Also, OIDs are needed for system objects, so using up all of the OIDs on row counters doesn't help the database with assigning OIDs to new tables (for the table, not its rows). Also, consider whether a singe 4-byte integer counter is really going to be sufficient for every table in your database.
    – FuzzyChef
    Jan 30, 2015 at 21:12
  • it is worth to mention, in most implementation of phpPgAdmin when creating a table, the option is checked disable as default, meaning in a matter of fact that this option is deprecated.
    – vdegenne
    Apr 27, 2015 at 21:08
  • 4
    if you don't know what OIDs are used for then you probably don't want to be using them.
    – vdegenne
    Jul 9, 2016 at 21:26