pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Dominique Devienne <ddevienne@gmail.com>
To: Francisco Olarte <folarte@peoplecall.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Sebastien Flaesch <sebastien.flaesch@4js.com>
Cc: Kirk Wolak <wolakk@gmail.com>
Cc: Geoff Winkless <pgsqladmin@geoff.dj>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Using CTID system column as a "temporary" primary key
Date: Thu, 30 Mar 2023 17:13:53 +0200
Message-ID: <9e27adafdc683c21b493b6fece4e50124307668f.camel@cybertec.at> (raw)
In-Reply-To: <CAFCRh-_1GBruWFy8T1Fw7RMK3BtCYLfQeDxsyirUbtrVfhPM3A@mail.gmail.com>
References: <DBAP191MB12891F47802C3674F77BF751B0889@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
	<CAEzk6feEGHzS_TexvevFmWhsYt7MYhUUEUE54stO7SPNbL_Ypg@mail.gmail.com>
	<DBAP191MB1289536BFF22FD8BD5FFEC61B0889@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
	<CACLU5mQ2XMd5c4tetUHacVE538c8nd1t2CWSfDvis2GCKg0L9A@mail.gmail.com>
	<DBAP191MB1289D8F0F4A8CB1423ADB05FB0899@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
	<b121c195-8122-3167-5e33-3bd325702df5@aklaver.com>
	<AM9P191MB1286CE763D59D8B821F69409B0899@AM9P191MB1286.EURP191.PROD.OUTLOOK.COM>
	<478a7768-6b11-dda5-ce82-5b9d39aa4923@aklaver.com>
	<CAFCRh-_rUDZt=vc4H+K9+__5io8N71qjyJ4nvrAeMAhcMMxOig@mail.gmail.com>
	<CA+bJJbyDM5ej+JYAgzybqnDti9PTjykAEGAymA81eXm+Gmfmxw@mail.gmail.com>
	<CAFCRh-_1GBruWFy8T1Fw7RMK3BtCYLfQeDxsyirUbtrVfhPM3A@mail.gmail.com>

On Thu, 2023-03-30 at 14:32 +0200, Dominique Devienne wrote:
> I fail to see that myself, sorry. You can bind NULL, you can bind values, so why
> wouldn't you be able to bind DEFAULT too? I see that more as a failing to the
> binding API myself :)

That doesn't work because DEFAULT is not a value like NULL, it is
a keyword.

> But I guess it can be worked around with something like
> `... values(coalesce($1, default), ..)`
> and abusing NULL to mean DEFAULT on a case-by-case bases.
> Assuming default can be used in this way (didn't try), of course.

That won't work either, because DEFAULT is a keyword and cannot used
in expressions.

For what you want, I can think of two approaches:

1. Use two prepared INSERT statements with different column lists,
   one with the column in question and the other without.
   Then use the appropriate statement, depending on whether you
   want the default value or not.

2. Don't use a default value, but a BEFORE INSERT trigger.
   If you insert some magical value like -1, the trigger replaces
   the value with some default.

The second solution is somewhat uglier (personal hudgement) and slower.

Yours,
Laurenz Albe





view thread (28+ messages)  latest in thread

Message-ID: <9e27adafdc683c21b493b6fece4e50124307668f.camel@cybertec.at>
Permalink:  ../9e27adafdc683c21b493b6fece4e50124307668f.camel@cybertec.at/
Also on:    postgresql.org/message-id/9e27adafdc683c21b493b6fece4e50124307668f.camel@cybertec.at

 · 

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: laurenz.albe@cybertec.at, ddevienne@gmail.com, folarte@peoplecall.com, adrian.klaver@aklaver.com, sebastien.flaesch@4js.com, wolakk@gmail.com, pgsqladmin@geoff.dj
  Subject: Re: Using CTID system column as a "temporary" primary key
  In-Reply-To: <9e27adafdc683c21b493b6fece4e50124307668f.camel@cybertec.at>
  References: <DBAP191MB12891F47802C3674F77BF751B0889@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM> <CAEzk6feEGHzS_TexvevFmWhsYt7MYhUUEUE54stO7SPNbL_Ypg@mail.gmail.com> <DBAP191MB1289536BFF22FD8BD5FFEC61B0889@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM> <CACLU5mQ2XMd5c4tetUHacVE538c8nd1t2CWSfDvis2GCKg0L9A@mail.gmail.com> <DBAP191MB1289D8F0F4A8CB1423ADB05FB0899@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM> <b121c195-8122-3167-5e33-3bd325702df5@aklaver.com> <AM9P191MB1286CE763D59D8B821F69409B0899@AM9P191MB1286.EURP191.PROD.OUTLOOK.COM> <478a7768-6b11-dda5-ce82-5b9d39aa4923@aklaver.com> <CAFCRh-_rUDZt=vc4H+K9+__5io8N71qjyJ4nvrAeMAhcMMxOig@mail.gmail.com> <CA+bJJbyDM5ej+JYAgzybqnDti9PTjykAEGAymA81eXm+Gmfmxw@mail.gmail.com> <CAFCRh-_1GBruWFy8T1Fw7RMK3BtCYLfQeDxsyirUbtrVfhPM3A@mail.gmail.com> <9e27adafdc683c21b493b6fece4e50124307668f.camel@cybertec.at>

* 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