Object identifier types v15
| Data type | Native | Alias | Description |
|---|---|---|---|
oid | ✅ | The numeric object identifier | |
regproc | ✅ | The name of the function | |
regprocedure | ✅ | The function with argument types | |
regoper | ✅ | The name of the operator | |
regoperator | ✅ | The operator with argument types | |
regclass | ✅ | The name of the relation | |
regtype | ✅ | The name of the data type | |
regconfig | ✅ | The text search configuration | |
regdictionary | ✅ | The text search dictionary |
Overview
Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables. OIDs aren't added to user-created tables, unless you specify WITH OIDS when you create the table or you enable the default_with_oids configuration variable. Type oid represents an object identifier. There are also several alias types for oid: regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig, and regdictionary.
The oid type is currently implemented as an unsigned four-byte integer. Therefore, it isn't large enough to provide database-wide uniqueness in large databases or even in large individual tables. So, we discourage using a user-created table's OID column as a primary key. OIDs are best used only for references to system tables.
The oid type itself has few operations beyond comparison. You can cast it to integer, however, and then manipulate it 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 can accept and display symbolic names for system objects, rather than the raw numeric value that type oid uses. The alias types allow simplified lookup of OID values for objects. For example, to examine the pg_attribute rows related to a table mytable, you can write:
SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;
This syntax is recommended over:
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 is 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 the "right thing." Similarly, casting a table's OID to regclass is handy for symbolic display of a numeric OID.
The following table lists the available object identifier types.
| Name | References | Description | Value example |
|---|---|---|---|
oid | Any | Numeric object identifier | 564182 |
regproc | pg_proc | Function name | sum |
regprocedure | pg_proc | Function with argument types | sum(int4) |
regoper | pg_operator | Operator name | + |
regoperator | pg_operator | Operator with argument types | *(integer,integer) or -(NONE,integer) |
regclass | pg_class | Relation name | pg_type |
regtype | pg_type | Data type name | integer |
regconfig | pg_ts_config | Text search configuration | english |
regdictionary | pg_ts_dict | Text search dictionary | simple |
All of the OID alias types accept schema-qualified names. They display schema-qualified names on output if you can't find the object in the current search path without qualifying it. The regproc and regoper alias types accept only input names that are unique (not overloaded), so they're of limited use. For most uses, regprocedure or regoperator are more appropriate. For regoperator, identify unary operators by using NONE for the unused operand.
An additional property 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 nextval('my_seq'::regclass), PostgreSQL understands that the default expression depends on the sequence my_seq. The system doesn't let the sequence be dropped without first removing the default expression.
Another identifier type used by the system is xid, or transaction (abbreviated xact) identifier. This is the data type of the system columns xmin and 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 cmin and 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 in its table.
For more information on system columns, see the PostgreSQL documentation.
- On this page
- Overview