Re: INSERT ... ON CONFLICT doesn't work - Mailing list pgsql-general

On 12/1/21 11:43, Jenda Krynicky wrote:
> From:               Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com