Object Identifier Type

A special data type serving as an alias for the OID of system catalogue table entries

An object identifier type (also known as OID alias type) is a special data type serving as an alias for the OID of various system catalogue table entries, and make it possible to reference row in such a table by the name of the object referenced in that row rather than the numeric OID. This greatly simplifies many operations involving common system catalogue lookups and removes the need to explicitly join system catalogue tables.

For example, casting a table row's tableoid system column to regclass will display the name of the table the row belongs to:

postgres=# SELECT tableoid::regclass, * FROM foo;
 tableoid | id 
----------+----
 foo      |  1
(1 row)

(The above example is of limited practical use, but if a similar query is run on a partitioned table, the table each row actually belongs to will be displayed).

Available object identifier types

As of PostgreSQL 15, following object identifier types are available:

  16 15 14 13 12 11 10 9.6 9.5 9.4 9.3 9.2 9.1 9.0 8.4 8.3 8.2 8.1 8.0 7.4 7.3 7.2 Function
regclass                                             to_regclass()
regcollation                                             to_regcollation()
regconfig                                             n/a
regdictionary                                             n/a
regnamespace                                             to_regnamespace()
regoper                                             to_regoper()
regoperator                                             to_regoperator()
regproc                                             to_regproc()
regprocedure                                             to_regprocedure()
regrole                                             to_regrole()
regtype                                             to_regtype()

Change history

Categories

Data type, System catalogue

See also

OID