Re: INSERT ... ON CONFLICT doesn't work

On 12/1/21 11:43, Jenda Krynicky wrote:
> From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
>> On 12/1/21 11:20 AM, Jenda Krynicky wrote:
>>> So let's suppose I have a table like this:
>>>
>>
>>>
>>> So pretty please with a cherry on top, how do I explain to postgres
>>> 13.4, that yes indeed by "Uuid" I mean the stinking column "Uuid".
>>
>> The basic issue is described here:
>>
>> https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-VAR-SUBST
>>
>> "Since the names of variables are syntactically no different from the
>> names of table columns, there can be ambiguity in statements that also
>> refer to tables: is a given name meant to refer to a table column, or a
>> variable? Let's change the previous example to ..."
>
> Looks like a bad design.

House rules.

My experience on this across a variety jobs software and not:

1) Learn the house rules

2) Do not expect them to follow your view of world.

3) Do not be surprised if the house does not follow it's own rules.

>
> While the ON CONFLICT () very explicitely insists on there being a
> name of a column of the table being inserted into. Makes nonsense.

No it does not expect this(house rules remember):

https://www.postgresql.org/docs/current/sql-insert.html

"
[ ON CONFLICT [ conflict_target ] conflict_action ]

where conflict_target can be one of:

( { index_column_name | ( index_expression ) } [ COLLATE collation ] [
opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
"

And further down:

https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

"conflict_target

Specifies which conflicts ON CONFLICT takes the alternative action
on by choosing arbiter indexes. Either performs unique index inference,
or names a constraint explicitly. For ON CONFLICT DO NOTHING, it is
optional to specify a conflict_target; when omitted, conflicts with all
usable constraints (and unique indexes) are handled. For ON CONFLICT DO
UPDATE, a conflict_target must be provide

...

index_column_name

The name of a table_name column. Used to infer arbiter indexes.
Follows CREATE INDEX format. SELECT privilege on index_column_name is
required.
index_expression

Similar to index_column_name, but used to infer expressions on
table_name columns appearing within index definitions (not simple
columns). Follows CREATE INDEX format. SELECT privilege on any column
appearing within index_expression is required.
collation

When specified, mandates that corresponding index_column_name or
index_expression use a particular collation in order to be matched
during inference. Typically this is omitted, as collations usually do
not affect whether or not a constraint violation occurs. Follows CREATE
INDEX format.
opclass

When specified, mandates that corresponding index_column_name or
index_expression use particular operator class in order to be matched
during inference. Typically this is omitted, as the equality semantics
are often equivalent across a type's operator classes anyway, or because
it's sufficient to trust that the defined unique indexes have the
pertinent definition of equality. Follows CREATE INDEX format.
index_predicate

Used to allow inference of partial unique indexes. Any indexes that
satisfy the predicate (which need not actually be partial indexes) can
be inferred. Follows CREATE INDEX format. SELECT privilege on any column
appearing within index_predicate is required.
constraint_name

Explicitly specifies an arbiter constraint by name, rather than
inferring a constraint or index.
condition

An expression that returns a value of type boolean. Only rows for
which this expression returns true will be updated, although all rows
will be locked when the ON CONFLICT DO UPDATE action is taken. Note that
condition is evaluated last, after a conflict has been identified as a
candidate to update.
"

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com