pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sebastien Flaesch <sebastien.flaesch@4js.com>
To: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Using CTID system column as a "temporary" primary key
Date: Tue, 28 Mar 2023 09:28:53 +0000
Message-ID: <DBAP191MB12891F47802C3674F77BF751B0889@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM> (raw)

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

view thread (28+ messages)  latest in thread

Message-ID: <DBAP191MB12891F47802C3674F77BF751B0889@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
Permalink:  ../DBAP191MB12891F47802C3674F77BF751B0889@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM/
Also on:    postgresql.org/message-id/DBAP191MB12891F47802C3674F77BF751B0889@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: sebastien.flaesch@4js.com
  Subject: Re: Using CTID system column as a "temporary" primary key
  In-Reply-To: <DBAP191MB12891F47802C3674F77BF751B0889@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
  References: <DBAP191MB12891F47802C3674F77BF751B0889@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox