<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>8.19. Object Identifier Types</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="domains.html" title="8.18. Domain Types" /><link rel="next" href="datatype-pg-lsn.html" title="8.20. pg_lsn Type" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">8.19. Object Identifier Types</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="domains.html" title="8.18. Domain Types">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="datatype.html" title="Chapter 8. Data Types">Up</a></td><th width="60%" align="center">Chapter 8. Data Types</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="datatype-pg-lsn.html" title="8.20. pg_lsn Type">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="DATATYPE-OID"><div class="titlepage"><div><div><h2 class="title" style="clear: both">8.19. Object Identifier Types</h2></div></div></div><a id="id-1.5.7.27.2" class="indexterm"></a><a id="id-1.5.7.27.3" class="indexterm"></a><a id="id-1.5.7.27.4" class="indexterm"></a><a id="id-1.5.7.27.5" class="indexterm"></a><a id="id-1.5.7.27.6" class="indexterm"></a><a id="id-1.5.7.27.7" class="indexterm"></a><a id="id-1.5.7.27.8" class="indexterm"></a><a id="id-1.5.7.27.9" class="indexterm"></a><a id="id-1.5.7.27.10" class="indexterm"></a><a id="id-1.5.7.27.11" class="indexterm"></a><a id="id-1.5.7.27.12" class="indexterm"></a><a id="id-1.5.7.27.13" class="indexterm"></a><a id="id-1.5.7.27.14" class="indexterm"></a><p>
Object identifiers (OIDs) are used internally by
<span class="productname">PostgreSQL</span> as primary keys for various
system tables. OIDs are not added to user-created tables, unless
<code class="literal">WITH OIDS</code> is specified when the table is
created, or the <a class="xref" href="runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS">default_with_oids</a>
configuration variable is enabled. Type <code class="type">oid</code> represents
an object identifier. There are also several alias types for
<code class="type">oid</code>: <code class="type">regproc</code>, <code class="type">regprocedure</code>,
<code class="type">regoper</code>, <code class="type">regoperator</code>, <code class="type">regclass</code>,
<code class="type">regtype</code>, <code class="type">regrole</code>, <code class="type">regnamespace</code>,
<code class="type">regconfig</code>, and <code class="type">regdictionary</code>.
<a class="xref" href="datatype-oid.html#DATATYPE-OID-TABLE" title="Table 8.24. Object Identifier Types">Table 8.24</a> shows an overview.
</p><p>
The <code class="type">oid</code> 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><p>
The <code class="type">oid</code> 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 <code class="type">oid</code> would use. The alias
types allow simplified lookup of OID values for objects. For example,
to examine the <code class="structname">pg_attribute</code> rows related to a table
<code class="literal">mytable</code>, 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
<code class="literal">mytable</code> in different schemas.
The <code class="type">regclass</code> input converter handles the table lookup according
to the schema path setting, and so it does the <span class="quote">“<span class="quote">right thing</span>”</span>
automatically. Similarly, casting a table's OID to
<code class="type">regclass</code> is handy for symbolic display of a numeric OID.
</p><div class="table" id="DATATYPE-OID-TABLE"><p class="title"><strong>Table 8.24. Object Identifier Types</strong></p><div class="table-contents"><table class="table" summary="Object Identifier Types" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>Name</th><th>References</th><th>Description</th><th>Value Example</th></tr></thead><tbody><tr><td><code class="type">oid</code></td><td>any</td><td>numeric object identifier</td><td><code class="literal">564182</code></td></tr><tr><td><code class="type">regproc</code></td><td><code class="structname">pg_proc</code></td><td>function name</td><td><code class="literal">sum</code></td></tr><tr><td><code class="type">regprocedure</code></td><td><code class="structname">pg_proc</code></td><td>function with argument types</td><td><code class="literal">sum(int4)</code></td></tr><tr><td><code class="type">regoper</code></td><td><code class="structname">pg_operator</code></td><td>operator name</td><td><code class="literal">+</code></td></tr><tr><td><code class="type">regoperator</code></td><td><code class="structname">pg_operator</code></td><td>operator with argument types</td><td><code class="literal">*(integer,integer)</code> or <code class="literal">-(NONE,integer)</code></td></tr><tr><td><code class="type">regclass</code></td><td><code class="structname">pg_class</code></td><td>relation name</td><td><code class="literal">pg_type</code></td></tr><tr><td><code class="type">regtype</code></td><td><code class="structname">pg_type</code></td><td>data type name</td><td><code class="literal">integer</code></td></tr><tr><td><code class="type">regrole</code></td><td><code class="structname">pg_authid</code></td><td>role name</td><td><code class="literal">smithee</code></td></tr><tr><td><code class="type">regnamespace</code></td><td><code class="structname">pg_namespace</code></td><td>namespace name</td><td><code class="literal">pg_catalog</code></td></tr><tr><td><code class="type">regconfig</code></td><td><code class="structname">pg_ts_config</code></td><td>text search configuration</td><td><code class="literal">english</code></td></tr><tr><td><code class="type">regdictionary</code></td><td><code class="structname">pg_ts_dict</code></td><td>text search dictionary</td><td><code class="literal">simple</code></td></tr></tbody></table></div></div><br class="table-break" /><p>
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.
The <code class="type">regproc</code> and <code class="type">regoper</code> alias types will only
accept input names that are unique (not overloaded), so they are
of limited use; for most uses <code class="type">regprocedure</code> or
<code class="type">regoperator</code> are more appropriate. For <code class="type">regoperator</code>,
unary operators are identified by writing <code class="literal">NONE</code> for the unused
operand.
</p><p>
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
expression <code class="literal">nextval('my_seq'::regclass)</code>,
<span class="productname">PostgreSQL</span>
understands that the default expression depends on the sequence
<code class="literal">my_seq</code>; the system will not let the sequence be dropped
without first removing the default expression.
<code class="type">regrole</code> is the only exception for the property. Constants of this
type are not allowed in such expressions.
</p><div class="note"><h3 class="title">Note</h3><p>
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.
</p></div><p>
Another identifier type used by the system is <code class="type">xid</code>, or transaction
(abbreviated <abbr class="abbrev">xact</abbr>) identifier. This is the data type of the system columns
<code class="structfield">xmin</code> and <code class="structfield">xmax</code>. Transaction identifiers are 32-bit quantities.
</p><p>
A third identifier type used by the system is <code class="type">cid</code>, or
command identifier. This is the data type of the system columns
<code class="structfield">cmin</code> and <code class="structfield">cmax</code>. Command identifiers are also 32-bit quantities.
</p><p>
A final identifier type used by the system is <code class="type">tid</code>, or tuple
identifier (row identifier). This is the data type of the system column
<code class="structfield">ctid</code>. 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 class="xref" href="ddl-system-columns.html" title="5.4. System Columns">Section 5.4</a>.)
</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="domains.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="datatype.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="datatype-pg-lsn.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">8.18. Domain Types </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 8.20. <acronym class="acronym">pg_lsn Type</acronym></td></tr></table></div></body></html>
