Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various
system tables. OIDs are not added to user-created tables, unless
WITH OIDS is specified when the
table is created, or the default_with_oids
configuration variable is enabled. Type
oid represents an object identifier. There are also
several alias types for
regdictionary. Table 8.24
shows an overview.
oid type is currently
implemented as an unsigned four-byte integer. Therefore, it is
not large enough to provide database-wide uniqueness in large
databases, or even in large individual tables. So, using a
user-created table's OID column as a primary key is discouraged.
OIDs are best used only for references to system tables.
oid type itself has few
operations beyond comparison. It can be cast to integer, however,
and then manipulated using the standard integer operators.
(Beware of possible signed-versus-unsigned confusion if you do
The OID alias types have no operations of their own except for
specialized input and output routines. These routines are able to
accept and display symbolic names for system objects, rather than
the raw numeric value that type
would use. The alias types allow simplified lookup of OID values
for objects. For example, to examine the
pg_attribute rows related to a table
mytable, one could write:
SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;
SELECT * FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');
While that doesn't look all that bad by itself, it's still
oversimplified. A far more complicated sub-select would be needed
to select the right OID if there are multiple tables named
mytable in different schemas. The
regclass input converter handles the
table lookup according to the schema path setting, and so it does
thing” automatically. Similarly, casting a table's
regclass is handy for symbolic
display of a numeric OID.
Table 8.24. Object Identifier Types
||any||numeric object identifier||
||function with argument types||
||operator with argument types||
||data type name||
||text search configuration||
||text search dictionary||
All of the OID alias types for objects grouped by namespace
accept schema-qualified names, and will display schema-qualified
names on output if the object would not be found in the current
search path without being qualified. The
types will only accept input names that are unique (not
overloaded), so they are of limited use; for most uses
regoperator are more appropriate. For
regoperator, unary operators are identified by
NONE for the unused
An additional property of most of the OID alias types is the
creation of dependencies. If a constant of one of these types
appears in a stored expression (such as a column default
expression or view), it creates a dependency on the referenced
object. For example, if a column has a default expression
PostgreSQL understands that the
default expression depends on the sequence
my_seq; the system will not let the sequence be
dropped without first removing the default expression.
regrole is the only exception for the
property. Constants of this type are not allowed in such
The OID alias types do not completely follow transaction isolation rules. The planner also treats them as simple constants, which may result in sub-optimal planning.
Another identifier type used by the system is
xid, or transaction (abbreviated xact) identifier. This is the data type of the
xmax. Transaction identifiers
are 32-bit quantities.
A third identifier type used by the system is
cid, or command identifier. This is the data type
of the system columns
cmax. Command identifiers are
also 32-bit quantities.
A final identifier type used by the system is
tid, or tuple identifier (row identifier). This is
the data type of the system column
ctid. A tuple ID is a pair (block number,
tuple index within block) that identifies the physical location
of the row within its table.
(The system columns are further explained in Section 5.4.)
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.