21

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.

Improve this question

2 Answers 2

Reset to default
20

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.

A final identifier type used by the system is tid, or tuple identifier (row identifier). This is the data type of the system column ctid. A tuple ID is a pair (block number, tuple index within block) that identifies the physical location of the row within its table.

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 Robert Haas Blog, Michael Paquier post, zheap announcement in pg hackers mailing list, Zheap PG Wiki, and Andres Anarazel youtube video in pgcon'19.

Improve this answer
3
  • I have an additional question regarding the tid type? It it comparable? For instance, can you use min(ctid) in a query?
    – Dirty Henry
    May 5, 2020 at 8:19
  • @DirtyHenry Why would you want to compare or rank the physical storage location of a tuple? If you have a serious use-case in mind, I suggest posting a Question here s Stack Exchange.
    – Basil Bourque
    May 5, 2020 at 14:11
  • 3
    @BasilBourque Actually he's right; if you google delete duplicate rows in postgres without unique id, you find people resort to ctid as almost the only way plausible.
    – stucash
    Jul 23, 2020 at 14:19
2

CTID is another name for ItemPointer. It's basically a pointer to the tuples stored on the page. See bufpage.h header comment in PostgreSQL source code.

Reference: https://www.postgresql.org/docs/14/storage-page-layout.html

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.