What is oid in postgresql table

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.

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 xid , or transaction abbreviated xact identifier. This is the data type of the system columns xmin and xmax. Transaction identifiers are 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 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. 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.

Development Versions: devel. Unsupported versions: 9. Data Types Home Next. Object Identifier Types. 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. The 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 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 oid would use.

The alias types allow simplified lookup of OID values for objects. 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 the "right thing" automatically.

All of the OID alias types 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 regproc and regoper alias types will only accept input names that are unique not overloaded , so they are of limited use; for most uses regprocedure or regoperator is more appropriate. For regoperator , unary operators are identified by writing NONE for the unused operand.

An additional property of the OID alias types is that 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. 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.