Using CTID system column as a "temporary" primary key

From: Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Using CTID system column as a "temporary" primary key
Date: 2023-03-28 09:28:53
Message-ID: DBAP191MB12891F47802C3674F77BF751B0889@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello!

We are looking for an equivalent of Informix ROWID or Oracle's ROWNUM.

Is the CTID a good choice?

I assume it must be used in a specific context, and of course not considered as permanent primary key.

I understand that if the row is updated, the CTID may change.

Where can we find details about the validity and lifetime of the value such column?

Will CTID be supported long term or is there any plan to remove it or hide it some day?

Of course, one should use a real primary key definition. However, we have legacy code to adapt to PostgreSQL, and in some cases, tables have a composite primary key. A first SELECT uses that primary key, but it also fetches the ROWID, and will use that one in a subsequent SELECT, UPDATE or DELETE, instead of carrying the composite pkey values.

Seb

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sengottaiyan T 2023-03-28 09:52:19 DB migration : Sybase to Postgres
Previous Message Laurenz Albe 2023-03-28 06:26:57 Re: Oracle to PostgreSQL Migration