Hi Geoff,
Your remark makes total sense, and this is what should be done.
However, we have to deal with quite complex legacy 4GL code that we prefer to not touch, and we can adapt the SQL statements on the fly with our solution (kind of Java compiler/runtime system).
Next question:
How can I UPDATE or DELETE a row, with the CTID column?
When I bind a string parameter, I get this error:
SQLSTATE = 42883
MESSAGE: operator does not exist: tid = character varying
Do I have to cast() ?
Seb
From: Geoff Winkless <pgsqladmin@geoff.dj>
Sent: Tuesday, March 28, 2023 12:20 PM
To: Sebastien Flaesch <sebastien.flaesch@4js.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Using CTID system column as a "temporary" primary key
EXTERNAL: Do not click links or open attachments if you do not recognize the sender.
Is the CTID a good choice?
I think if you're honest with yourself you already know the answer to this question. The only real solution is to update the legacy code to use the primary key, or (if that's not possible) change the table definition to add your own indexed BIGSERIAL value called "ROWID" to the rows and use that instead (assuming it will be large enough).
Geoff