fabrizioM fabrizioM - 1 month ago 10
SQL Question

SQL, Postgres OIDs, What are they and why are they useful?

I am looking at some PostgreSQL table creation and I stumbled upon this:


I read the documentation provided by postgres and I know the concept of object identifier from OOP but still I do not grasp,

  • why such identifier would be useful in a database?

  • to make queries shorter?

  • when should it be used?


OIDs basically give you a built-in, globally unique 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.

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.