The Postgres system columns are documented in Chapter 5. Data Definition > 5.4. System Columns.
That page mentions that oid
values “are 32-bit quantities”. And that page says the same about transaction identifiers. So I will assume that means oid
, tableoid
, xmin
, cmin
, xmax
, and cmax
are all 32-bit integers.
But that leaves the ctid
system column.
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 if 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.
➡ What is the data type of the ctid
column?
Specifically I am interested in version Postgres 10.3, but if it has changed over past versions, that would be good to know.
Best Answer
tid
See the manual page, Chapter 8. Data Types > 8.18. Object Identifier Types. It explains that the data type is Postgres-specific, and known as
tid
.You might find this similar Question interesting: How do I decompose ctid into page and row numbers?
By the way, if you are interested in this topic of
ctid
&tid
, you might likely be interested in two new features of Postgres 12: (a) OIDs demoted to normal columns, and (b) the pluggable table storage / Access Methods feature new in Postgres 12 and later. See this, this, this, this, and this.