Re: distinguish update from insert (on conflict)

On 5/21/19 7:37 PM, Justin Pryzby wrote:
> On Tue, May 21, 2019 at 06:57:36PM -0700, Adrian Klaver wrote:
>> On 5/21/19 6:34 PM, Justin Pryzby wrote:
>>> Is it still impossible to distinguish whether a row was inserted vs updated ?
>>
>> You will need to be more specific.
>
> Sorry, I mean with UPSERT / "INSERT .. ON CONFLICT DO UPDATE", is it possible
> to tell whether a row was inserted vs. updated ?

In addition to Fabio's suggestion, from my previous post:

https://www.postgresql.org/docs/10/sql-createtrigger.html

"n some cases it is possible for a single SQL command to fire more than
one kind of trigger. For instance an INSERT with an ON CONFLICT DO
UPDATE clause may cause both insert and update operations, so it will
fire both kinds of triggers as needed. The transition relations supplied
to triggers are specific to their event type; thus an INSERT trigger
will see only the inserted rows, while an UPDATE trigger will see only
the updated rows."

So you might want to check out triggers using transition tables.

>
> Thanks,
> Justin
>

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