Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




Databases - Practical PostgreSQL
Previous Page Home Next Page

System Columns

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



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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

  Published under the terms of the Open Publication License Design by Interspire