8.19. Object Identifier Types
Object identifiers (OIDs) are used internally by
as primary keys for various
represents an object identifier. There are also
several alias types for
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
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 this.)
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
rows related to a table
, 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
in different schemas.
input converter handles the table lookup according
to the schema path setting, and so it does the
automatically. Similarly, casting a table's OID to
is handy for symbolic display of a numeric OID.
Table 8.26. Object Identifier Types
||any||numeric object identifier||
||text search configuration||
||text search dictionary||
||operator with argument types||
||function with argument types||
||data type name||
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.
alias types will only
accept input names that are unique (not overloaded), so they are
of limited use; for most uses
are more appropriate. For
unary operators are identified by writing
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
understands that the default expression depends on the sequence
; the system will not let the sequence be dropped
without first removing the default expression.
is the only exception for the property. Constants of this
type are not allowed in such expressions.
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
, or transaction
) identifier. This is the data type of the system columns
. Transaction identifiers are 32-bit quantities.
In some contexts, a 64-bit variant
is used. Unlike
values increase strictly
monotonically and cannot be reused in the lifetime of a database cluster.
A third identifier type used by the system is
command identifier. This is the data type of the system columns
. Command identifiers are also 32-bit quantities.
A final identifier type used by the system is
, or tuple
identifier (row identifier). This is the data type of the system column
. 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.5 .)