PostgreSQL defines a series of
system columns
in all tables, which are normally invisible to the
user (e.g., they will not be shown by queries unless explicitly requested). These columns contain
meta-data
about the content of the table's rows. Many of these contain data that can help to
differentiate between
tuples
(an individual state of a row) when working with transaction blocks.
(See Chapter 7 for more about transactions.)
As a result of these system-defined columns, in addition to the user-defined columns of a table, any inserted row
will have values in each of the columns described in Table 3-25.
Table 3-25. System columns
|
Column
|
Description
|
|
oid (object identifier)
|
The unique object identifier of a row. PostgreSQL automatically adds this 4-byte number to
all rows. It is never re-used within the same table.
|
|
tableoid (table object identifier)
|
The oid of the table that contains a row. The name and oid of a table are
related by the pg_class system table.
|
|
xmin (transaction minimum)
|
The transaction identifier of the inserting transaction of a tuple.
|
|
cmin (command minimum)
|
The command identifier, starting at 0, associated with the inserting transaction of a tuple.
|
|
xmax (transaction maximum)
|
The transaction identifier of a tuple's deleting transaction. If a tuple is visible (has not been deleted) this is set to zero.
|
|
cmax (command maximum)
|
The command identifier associated with the deleting transaction of a tuple. Like xmax, if a tuple is visible, this is set to zero.
|
|
ctid (tuple identifier)
|
The identifier which describes the physical location of the tuple within the database. A pair of numbers are represented by the ctid: the block number, and tuple index within that block.
|
