ROWID columns

Informix®

When creating a table, Informix automatically adds a ROWID integer column (applies to non-fragmented tables only).

The ROWID column is auto-filled with a unique number and can be used like a primary key to access a given row.

Note: Informix ROWID usage was a common practice in the early days of Informix 4GL programming. Today it is recommended to define all your database tables with a PRIMARY KEY to uniquely identify rows.

With Informix, the SQLCA.SQLERRD[6] register contains the ROWID of the last row affected by an INSERT, UPDATE or DELETE statement.

PostgreSQL

With PostgreSQL versions prior to version 12, tables can be created with an OID (Object Identifier) column, that is similar to Informix ROWID columns.

Important: Starting with PostgreSQL version 12, OID columns are no longer supported, and the ROWID emulation is no longer possible with the PostgreSQL ODI driver.

The type of PostgreSQL OID columns is INTEGER.

Important: Since PostgreSQL version 8.1, OID columns are no longer enabled by default. To have OID columns created for tables, you need to set the default_with_oid parameter to "on" in the postgresql.conf file.

The PostgreSQL C API provides the PQoidValue() function, to get the OID of the last inserted row. Unlike Informix SQLCA.SQLERRD[6], PostgreSQL does not return the OID of the last row modified by an UPDATE, or removed by a DELETE.

Solution

For PostgreSQL versions prior to version 12, the database can automatically convert ROWID keywords to OID.

If PostgreSQL OID columns are available and enabled in the database, SQL statements such as "SELECT ROWID FROM" and "UPDATE .. WHERE ROWID = ?" will work as with Informix.

To enable OID columns with PostgreSQL versions prior to version 12, the default_with_oid PostgreSQL server parameter must be set to "on". See Database configuration and design tasks for more details.

Note: SQLCA.SQLERRD[6] is partly supported: It works only for INSERT statements. As PostgreSQL does not return the OID of the last row modified by an UPDATE, or removed by a DELETE, this register will not contain the OID of the last modified or deleted row. All references to SQLCA.SQLERRD[6] should be reviewed.

As a general programming pattern, consider using PRIMARY KEY columns to identify database table rows.

For databases where the keyword of the rowid pseudo-column is different than "ROWID", the translation can be controlled with the following FGLPROFILE entry:
dbi.database.dsname.ifxemul.rowid = { true | false }
For more details see IBM Informix emulation parameters in FGLPROFILE.