Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables. The oid type represents a numeric object identifier. Several alias types also exist for oid, each prefixed with reg, that let you reference system objects by name instead of raw numeric value.
OID alias type reference
| Name | References | Description | Example |
|---|---|---|---|
oid | any | Numeric object identifier | 564182 |
regclass | pg_class | Relation name | pg_type |
regcollation | pg_collation | Collation name | "POSIX" |
regconfig | pg_ts_config | Text search configuration | english |
regdictionary | pg_ts_dict | Text search dictionary | simple |
regnamespace | pg_namespace | Namespace name | pg_catalog |
regoper | pg_operator | Operator name | + |
regoperator | pg_operator | Operator with argument types | *(integer,integer) or -(NONE,integer) |
regproc | pg_proc | Function name | sum |
regprocedure | pg_proc | Function with argument types | sum(int4) |
regrole | pg_authid | Role name | smithee |
regtype | pg_type | Data type name | integer |
The oid type
oid is implemented as an unsigned four-byte integer. It is not large enough to guarantee uniqueness across a large database or within a large individual table. Beyond comparison, oid has few built-in operations — but it can be cast to integer and used with standard integer operators. Be aware of potential signed-versus-unsigned confusion when doing this.
OID alias type behavior
OID alias types have no operations of their own beyond specialized input and output routines. Those routines accept and display symbolic names for system objects rather than raw numeric OID values.
The key advantage is simplified lookup. For example, to query pg_attribute rows for a table named mytable, write:
SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;instead of:
SELECT * FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');The regclass input converter handles schema path resolution automatically. When multiple tables named mytable exist in different schemas, it applies the current search path to select the correct one. Casting a table's OID to regclass also works in the other direction — it gives you the symbolic name for a numeric OID.
Schema-qualified names
All OID alias types for namespace-grouped objects accept schema-qualified names as input, such as myschema.mytable for regclass. Output uses a schema-qualified name when the object would not be found in the current search path without qualification.
regproc and regoper only accept input names that are unique (not overloaded), so their use is limited. For most cases, use regprocedure or regoperator instead. For regoperator, identify unary operators by writing NONE for the unused operand.
Dependencies in stored expressions
When a constant of an OID alias type appears in a stored expression — such as a column default expression or a view — it creates a dependency on the referenced object. For example:
nextval('my_seq'::regclass)PostgreSQL treats this default expression as depending on the sequence my_seq. The system prevents my_seq from being dropped until the default expression is removed first.
regrole is an exception: constants of this type are not allowed in stored expressions.
OID alias types do not fully follow transaction isolation rules. The query planner treats them as simple constants, which may result in suboptimal query plans.
Other system identifier types
In addition to oid, PostgreSQL uses three other internal identifier types.
Transaction identifier (`xid`) — The data type of the system columns xmin and xmax. Transaction identifiers are 32-bit quantities. In some contexts, a 64-bit variant, xid8, is used. Unlike xid values, xid8 values increase strictly monotonically and cannot be reused within the lifetime of a database cluster.
Command identifier (`cid`) — The data type of the system columns cmin and cmax. Command identifiers are also 32-bit quantities.
Tuple identifier (`tid`, also called row identifier) — 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 a row within its table.