Re: How do I upsert depending on a second table?

On 9/23/25 17:25, Juan Rodrigo Alejandro Burgos Mella wrote:
> Hi Samuel
>
> Using ON CONFLICT is a headache.

Like any tool ON CONFLICT has usage it is best for, if you try to force
it do something it was not designed for then it will not perform as
expected. Stick to what it good at and it will not be a headache. It is
good at moving data into a table where the incoming data is a mix of
entirely new rows and changes to existing rows for which there is some
sort of arbiter to decide on whether there is a conflict or not.

> It's better to use the versatility of a Trigger: you have the full
> record at your fingertips, and if you're going to UPDATE, you have the
> previous record too.

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

"
The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the
existing row using the table's name (or an alias), and to the row
proposed for insertion using the special excluded table.
"

> There's much more control.
>
> Also, you can always count on the beloved foreign keys, which are also
> quite useful.
>
> Atte.
> JRBM
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com