<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Object Identifier Types</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.0.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Data Types" HREF="datatype.html"><LINK REL="PREVIOUS" TITLE="Composite Types" HREF="rowtypes.html"><LINK REL="NEXT" TITLE="Pseudo-Types" HREF="datatype-pseudo.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2007-02-02T03:57:22"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="rowtypes.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="datatype.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 8. Data Types</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="datatype.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="datatype-pseudo.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="DATATYPE-OID" >8.12. Object Identifier Types</A ></H1 ><A NAME="AEN5631" ></A ><A NAME="AEN5634" ></A ><A NAME="AEN5636" ></A ><A NAME="AEN5638" ></A ><A NAME="AEN5640" ></A ><A NAME="AEN5642" ></A ><A NAME="AEN5644" ></A ><A NAME="AEN5646" ></A ><A NAME="AEN5648" ></A ><A NAME="AEN5650" ></A ><A NAME="AEN5652" ></A ><P > Object identifiers (OIDs) are used internally by <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > as primary keys for various system tables. An OID system column is also added to user-created tables, unless <TT CLASS="LITERAL" >WITHOUT OIDS</TT > is specified when the table is created, or the <A HREF="runtime-config.html#GUC-DEFAULT-WITH-OIDS" >default_with_oids</A > configuration variable is set to false. Type <TT CLASS="TYPE" >oid</TT > represents an object identifier. There are also several alias types for <TT CLASS="TYPE" >oid</TT >: <TT CLASS="TYPE" >regproc</TT >, <TT CLASS="TYPE" >regprocedure</TT >, <TT CLASS="TYPE" >regoper</TT >, <TT CLASS="TYPE" >regoperator</TT >, <TT CLASS="TYPE" >regclass</TT >, and <TT CLASS="TYPE" >regtype</TT >. <A HREF="datatype-oid.html#DATATYPE-OID-TABLE" >Table 8-19</A > shows an overview. </P ><P > The <TT CLASS="TYPE" >oid</TT > 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. </P ><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > OIDs are included by default in user-created tables in <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > 8.0.11. However, this behavior is likely to change in a future version of <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN >. Eventually, user-created tables will not include an OID system column unless <TT CLASS="LITERAL" >WITH OIDS</TT > is specified when the table is created, or the <TT CLASS="VARNAME" >default_with_oids</TT > configuration variable is set to true. If your application requires the presence of an OID system column in a table, it should specify <TT CLASS="LITERAL" >WITH OIDS</TT > when that table is created to ensure compatibility with future releases of <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN >. </P ></BLOCKQUOTE ></DIV ><P > The <TT CLASS="TYPE" >oid</TT > 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.) </P ><P > 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 <TT CLASS="TYPE" >oid</TT > would use. The alias types allow simplified lookup of OID values for objects. For example, to examine the <TT CLASS="STRUCTNAME" >pg_attribute</TT > rows related to a table <TT CLASS="LITERAL" >mytable</TT >, one could write </P><PRE CLASS="PROGRAMLISTING" >SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;</PRE ><P> rather than </P><PRE CLASS="PROGRAMLISTING" >SELECT * FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');</PRE ><P> 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 <TT CLASS="LITERAL" >mytable</TT > in different schemas. The <TT CLASS="TYPE" >regclass</TT > input converter handles the table lookup according to the schema path setting, and so it does the <SPAN CLASS="QUOTE" >"right thing"</SPAN > automatically. Similarly, casting a table's OID to <TT CLASS="TYPE" >regclass</TT > is handy for symbolic display of a numeric OID. </P ><DIV CLASS="TABLE" ><A NAME="DATATYPE-OID-TABLE" ></A ><P ><B >Table 8-19. Object Identifier Types</B ></P ><TABLE BORDER="1" CLASS="CALSTABLE" ><COL><COL><COL><COL><THEAD ><TR ><TH >Name</TH ><TH >References</TH ><TH >Description</TH ><TH >Value Example</TH ></TR ></THEAD ><TBODY ><TR ><TD ><TT CLASS="TYPE" >oid</TT ></TD ><TD >any</TD ><TD >numeric object identifier</TD ><TD ><TT CLASS="LITERAL" >564182</TT ></TD ></TR ><TR ><TD ><TT CLASS="TYPE" >regproc</TT ></TD ><TD ><TT CLASS="STRUCTNAME" >pg_proc</TT ></TD ><TD >function name</TD ><TD ><TT CLASS="LITERAL" >sum</TT ></TD ></TR ><TR ><TD ><TT CLASS="TYPE" >regprocedure</TT ></TD ><TD ><TT CLASS="STRUCTNAME" >pg_proc</TT ></TD ><TD >function with argument types</TD ><TD ><TT CLASS="LITERAL" >sum(int4)</TT ></TD ></TR ><TR ><TD ><TT CLASS="TYPE" >regoper</TT ></TD ><TD ><TT CLASS="STRUCTNAME" >pg_operator</TT ></TD ><TD >operator name</TD ><TD ><TT CLASS="LITERAL" >+</TT ></TD ></TR ><TR ><TD ><TT CLASS="TYPE" >regoperator</TT ></TD ><TD ><TT CLASS="STRUCTNAME" >pg_operator</TT ></TD ><TD >operator with argument types</TD ><TD ><TT CLASS="LITERAL" >*(integer,integer)</TT > or <TT CLASS="LITERAL" >-(NONE,integer)</TT ></TD ></TR ><TR ><TD ><TT CLASS="TYPE" >regclass</TT ></TD ><TD ><TT CLASS="STRUCTNAME" >pg_class</TT ></TD ><TD >relation name</TD ><TD ><TT CLASS="LITERAL" >pg_type</TT ></TD ></TR ><TR ><TD ><TT CLASS="TYPE" >regtype</TT ></TD ><TD ><TT CLASS="STRUCTNAME" >pg_type</TT ></TD ><TD >data type name</TD ><TD ><TT CLASS="LITERAL" >integer</TT ></TD ></TR ></TBODY ></TABLE ></DIV ><P > 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 <TT CLASS="TYPE" >regproc</TT > and <TT CLASS="TYPE" >regoper</TT > alias types will only accept input names that are unique (not overloaded), so they are of limited use; for most uses <TT CLASS="TYPE" >regprocedure</TT > or <TT CLASS="TYPE" >regoperator</TT > is more appropriate. For <TT CLASS="TYPE" >regoperator</TT >, unary operators are identified by writing <TT CLASS="LITERAL" >NONE</TT > for the unused operand. </P ><P > Another identifier type used by the system is <TT CLASS="TYPE" >xid</TT >, or transaction (abbreviated <ABBR CLASS="ABBREV" >xact</ABBR >) identifier. This is the data type of the system columns <TT CLASS="STRUCTFIELD" >xmin</TT > and <TT CLASS="STRUCTFIELD" >xmax</TT >. Transaction identifiers are 32-bit quantities. </P ><P > A third identifier type used by the system is <TT CLASS="TYPE" >cid</TT >, or command identifier. This is the data type of the system columns <TT CLASS="STRUCTFIELD" >cmin</TT > and <TT CLASS="STRUCTFIELD" >cmax</TT >. Command identifiers are also 32-bit quantities. </P ><P > A final identifier type used by the system is <TT CLASS="TYPE" >tid</TT >, or tuple identifier (row identifier). This is the data type of the system column <TT CLASS="STRUCTFIELD" >ctid</TT >. A tuple ID is a pair (block number, tuple index within block) that identifies the physical location of the row within its table. </P ><P > (The system columns are further explained in <A HREF="ddl-system-columns.html" >Section 5.4</A >.) </P ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="rowtypes.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="datatype-pseudo.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Composite Types</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="datatype.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Pseudo-Types</TD ></TR ></TABLE ></DIV ></BODY ></HTML >