Is there an open-source SQL database that has the concept of Oracle's "rowid" pseudo-column?

Question

I am aware that MySQL and PostgreSQL[1] do not have that concept, so I am interested in finding out of there is an open-source SQL database that does have the concept.

[1] It was later pointed out that PostgreSQL does have the rowid pseudo-column.

Answer 1

if neither of them have done that, then no others do.

Answer 2

SQLite has an auto incremented "ROWID" column which you can access using ROWID, _ROWID, or OID. If you define an integer primary key than they will be aliased.

Answer 3

PostgreSQL does have this concept.

See here for a brief list of pseudocolumns in PostgreSQL, out of which ctid is of interest to you:

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.

That is direct analog of Oracle's rowid.

As for MySQL, physical location of a row is not available for the front end.

In MyISAM, rowid is just a file offset from the beginning, and that's what is stored in the index leaves.

In InnoDB, tables are index organized by design, that means they always have some kind of a primary key, and the indexes over an InnoDB table use that PRIMARY KEY as a row pointer.

This is also true for Oracle's index organized tables, for which a rowid is not a physical pointer to a block in a datafile, but rather a such called logical ROWID, or UROWID

If you select a ROWID from an INDEX ORGANIZED table in Oracle, you will see that it has a different format (something like *BAEBwPICwQL+). This is in fact an encoded PRIMARY KEY value.

Note that if you have not defined any column as a PRIMARY KEY, MySQL will create a hidden surrogate PRIMARY KEY over which you will never have any control.

That's why you should always create some kind of a PRIMARY KEY in an InnoDB table: it's free, and you get control over the column.