Every table has several system columns 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 that they exist.
- oid
The object identifier (object ID) of a row. This is a serial
number that can be generated against all rows of a table rows if we
explicitly create a table using the WITH OIDS clause.
- xmin
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.)
- cmin
The command identifier (starting at zero) within the inserting transaction.
- xmax
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.
- cmax
The command identifier within the deleting transaction, or zero.
- ctid
The physical location of the row version within its table.
Note that although the ctid can be used to locate the row version very quickly,
a row's ctid will change each time it is updated or moved by
VACUUM FULL.
Therefore ctid 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.
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 the following
precaution is taken:
