Tables in PostgreSQL
If you are already familiar with SQL or other RDBMS packages, you probably already have a solid understanding of many of the relational database concepts put forth in this chapter. However, each RDBMS handles tables differently at the system level. This section takes a closer look at tables as they are implemented in PostgreSQL.
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
Column |
Description |
---|---|
|
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. |
|
The oid of the table that contains a row. The name and oid of a table are related by
the |
|
The transaction identifier of the inserting transaction of a tuple. |
|
The command identifier, starting at ... |
Get Practical PostgreSQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.