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.
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
Until PostgreSQL version 11, the OID
system columns can be enabled when setting
the default_with_oids
server configuration parameter. OID
columns
are unsigned integers that can be used as unique row identifier. Starting with PostgreSQL version
12, OID
columns are no longer supported. The type of PostgreSQL
OID
columns is INTEGER
. 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
. Starting with PostgreSQL version
12, OID system columns are no longer supported by PostgreSQL.
PostgreSQL tables have a CTID system column, but the value of this column defines the storage location of the row, the value changes when updating.
Solution
The PostgreSQL database driver automatically converts ROWID
keywords to
OID
, assuming that the PostgreSQL version supports user-table OIDs.
Starting with PostgreSQL 12, user-table OIDs are no longer supported, and therefore, ROWID columns can no longer be emulated.
As a general SQL programming pattern, get rid of ROWID usage and use PRIMARY KEY
columns to identify database table rows.
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.