System Columns#

Every table has several system columns that are implicitly defined by the system:

  • tableoid: The OID of the table containing this row.

    • Handy for queries that select from partitioned tables or inheritance heirachies (tells which particular table a row came from)

    • Can be joined with the oid column of pg_class to obtain the table name.

  • xmin: The identity (transaction ID) of the inserting transaction for this row version.

  • 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. If non-zero in a visible row version, this signifies an uncommitted or rolled-back deleting transaction.

  • cmax: The command identifier within the deleting transaction, or zero.

  • ctid: The physical location of the row within its table. Changes if a row is updated or moved by VACUUM FULL.

mydb=> CREATE TABLE alphabet (letter char(1));
CREATE TABLE
mydb=> INSERT INTO alphabet VALUES ('a'), ('b');
INSERT 0 2
mydb=> INSERT INTO alphabet VALUES ('c'), ('d');
INSERT 0 2
mydb=> SELECT letter, tableoid, xmin, cmin, xmax, cmax, ctid FROM alphabet;
 letter | tableoid | xmin | cmin | xmax | cmax | ctid  
--------+----------+------+------+------+------+-------
 a      |    17026 | 1038 |    0 |    0 |    0 | (0,1)
 b      |    17026 | 1038 |    0 |    0 |    0 | (0,2)
 c      |    17026 | 1039 |    0 |    0 |    0 | (0,3)
 d      |    17026 | 1039 |    0 |    0 |    0 | (0,4)
(4 rows)

Transaction IDs are 32-bit quantities. Uniqueness is not guaranteed for over a billion transactions.

Command identifiers are also 32-bit quantities, limiting each transaction to 2^32 (4,294,967,296) SQL commands. Only commands that actually modify the database contents will consume a command identifier.