What are system columns used for in PostgreSQL?

Question

I was following a PostgreSQL tutorial and the author just mentioned that there are system columns in PostgreSQL but did not mention when and why to use them.

Also, in PostgreSQL Documentation it just mentions that there are system columns but does not state when and why to use them?

What are the uses of system columns in PostgreSQL?

Answer 1

System columns are primarily meant to be used by the system. These columns are implementation details that might change between major versions (though very unlikely for most of them). You won't find a lot in the manual for when and why to use them because you are not supposed to use them in standard operation. The manual says:

You do not really need to be concerned about these columns; just know they exist.

That said, there are various special situations and corner cases where each of these columns come in handy. Remember that SELECT * never includes system columns. You have to list them explicitly if you want them in the SELECT list.

For instance, you can use the ctid (tuple ID) to uniquely identify rows (within the same statement) in the absence of a primary key within the same statement:

• Numbering rows consecutively for a number of tables

Search for Postgres in combination with each system column here on dba.SE or on stackoverflow to find various other use cases. Example search.

In addition to the manual page about system columns you are linking to, there is at least one other that's relevant:

• https://www.postgresql.org/docs/current/static/storage-page-layout.html