| Server IP : 104.21.25.180 / Your IP : 162.159.115.42 Web Server : Apache/2.4.37 System : Linux almalinux.duckdns.org 4.18.0-553.111.1.el8_10.x86_64 #1 SMP Sun Mar 8 20:06:07 EDT 2026 x86_64 User : ricodeal ( 1046) PHP Version : 7.4.33 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON Directory : /usr/share/doc/postgresql-docs/html/ |
Upload File : |
<?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>5.4. System Columns</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="[email protected]" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="ddl-constraints.html" title="5.3. Constraints" /><link rel="next" href="ddl-alter.html" title="5.5. Modifying Tables" /></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">5.4. System Columns</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ddl-constraints.html" title="5.3. Constraints">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><th width="60%" align="center">Chapter 5. Data Definition</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 10.23 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ddl-alter.html" title="5.5. Modifying Tables">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="DDL-SYSTEM-COLUMNS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">5.4. System Columns</h2></div></div></div><p> Every table has several <em class="firstterm">system columns</em> that are
implicitly defined by the system. Therefore, these names cannot be
used as names of user-defined columns. (Note that these
restrictions are separate from whether the name is a key word or
not; quoting a name will not allow you to escape these
restrictions.) You do not really need to be concerned about these
columns; just know they exist.
</p><a id="id-1.5.4.6.3" class="indexterm"></a><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="structfield">oid</code></span></dt><dd><p> <a id="id-1.5.4.6.4.1.2.1.1" class="indexterm"></a>
The object identifier (object ID) of a row. This column is only
present if the table was created using <code class="literal">WITH
OIDS</code>, or if the <a class="xref" href="runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS">default_with_oids</a>
configuration variable was set at the time. This column is of type
<code class="type">oid</code> (same name as the column); see <a class="xref" href="datatype-oid.html" title="8.18. Object Identifier Types">Section 8.18</a> for more information about the type.
</p></dd><dt><span class="term"><code class="structfield">tableoid</code></span></dt><dd><a id="id-1.5.4.6.4.2.2.1" class="indexterm"></a><p> The OID of the table containing this row. This column is
particularly handy for queries that select from inheritance
hierarchies (see <a class="xref" href="ddl-inherit.html" title="5.9. Inheritance">Section 5.9</a>), since without it,
it's difficult to tell which individual table a row came from. The
<code class="structfield">tableoid</code> can be joined against the
<code class="structfield">oid</code> column of
<code class="structname">pg_class</code> to obtain the table name.
</p></dd><dt><span class="term"><code class="structfield">xmin</code></span></dt><dd><a id="id-1.5.4.6.4.3.2.1" class="indexterm"></a><p> The identity (transaction ID) of the inserting transaction for
this row version. (A row version is an individual state of a
row; each update of a row creates a new row version for the same
logical row.)
</p></dd><dt><span class="term"><code class="structfield">cmin</code></span></dt><dd><a id="id-1.5.4.6.4.4.2.1" class="indexterm"></a><p> The command identifier (starting at zero) within the inserting
transaction.
</p></dd><dt><span class="term"><code class="structfield">xmax</code></span></dt><dd><a id="id-1.5.4.6.4.5.2.1" class="indexterm"></a><p> The identity (transaction ID) of the deleting transaction, or
zero for an undeleted row version. It is possible for this column to
be nonzero in a visible row version. That usually indicates that the
deleting transaction hasn't committed yet, or that an attempted
deletion was rolled back.
</p></dd><dt><span class="term"><code class="structfield">cmax</code></span></dt><dd><a id="id-1.5.4.6.4.6.2.1" class="indexterm"></a><p> The command identifier within the deleting transaction, or zero.
</p></dd><dt><span class="term"><code class="structfield">ctid</code></span></dt><dd><a id="id-1.5.4.6.4.7.2.1" class="indexterm"></a><p> The physical location of the row version within its table. Note that
although the <code class="structfield">ctid</code> can be used to
locate the row version very quickly, a row's
<code class="structfield">ctid</code> will change if it is
updated or moved by <code class="command">VACUUM FULL</code>. Therefore
<code class="structfield">ctid</code> is useless as a long-term row
identifier. The OID, or even better a user-defined serial
number, should be used to identify logical rows.
</p></dd></dl></div><p> OIDs are 32-bit quantities and are assigned from a single
cluster-wide counter. In a large or long-lived database, it is
possible for the counter to wrap around. Hence, it is bad
practice to assume that OIDs are unique, unless you take steps to
ensure that this is the case. If you need to identify the rows in
a table, using a sequence generator is strongly recommended.
However, OIDs can be used as well, provided that a few additional
precautions are taken:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> A unique constraint should be created on the OID column of each
table for which the OID will be used to identify rows. When such
a unique constraint (or unique index) exists, the system takes
care not to generate an OID matching an already-existing row.
(Of course, this is only possible if the table contains fewer
than 2<sup>32</sup> (4 billion) rows, and in practice the
table size had better be much less than that, or performance
might suffer.)
</p></li><li class="listitem"><p> OIDs should never be assumed to be unique across tables; use
the combination of <code class="structfield">tableoid</code> and row OID if you
need a database-wide identifier.
</p></li><li class="listitem"><p> Of course, the tables in question must be created <code class="literal">WITH
OIDS</code>. As of <span class="productname">PostgreSQL</span> 8.1,
<code class="literal">WITHOUT OIDS</code> is the default.
</p></li></ul></div><p>
</p><p> Transaction identifiers are also 32-bit quantities. In a
long-lived database it is possible for transaction IDs to wrap
around. This is not a fatal problem given appropriate maintenance
procedures; see <a class="xref" href="maintenance.html" title="Chapter 24. Routine Database Maintenance Tasks">Chapter 24</a> for details. It is
unwise, however, to depend on the uniqueness of transaction IDs
over the long term (more than one billion transactions).
</p><p> Command identifiers are also 32-bit quantities. This creates a hard limit
of 2<sup>32</sup> (4 billion) <acronym class="acronym">SQL</acronym> commands
within a single transaction. In practice this limit is not a
problem — note that the limit is on the number of
<acronym class="acronym">SQL</acronym> commands, not the number of rows processed.
Also, only commands that actually modify the database contents will
consume a command identifier.
</p></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ddl-constraints.html" title="5.3. Constraints">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ddl-alter.html" title="5.5. Modifying Tables">Next</a></td></tr><tr><td width="40%" align="left" valign="top">5.3. Constraints </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 10.23 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 5.5. Modifying Tables</td></tr></table></div></body></html>