Re: Using CTID system column as a "temporary" primary key

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