0

The on conflict used to implement a upsert behavior is a wonderful feature. I'm wondering if I were to find the RFC for the feature whether I might find a way to use the on conflict event to call a function, or use something like:

on conflict
do $$
insert into another table
$$

Otherwise, considering myself still new to postgresql, is there a way to "catch" the conflict to enable further intervention.

1 Answer 1

4

You cannot use the on conflict keyword to influence other tables. As stated by the official documentation, the conflict_action may be one of the following only:

conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

However, this does not mean there are no workarounds (at least for the do nothing command). You may use a function with the combination of the very useful found keyword in PostgreSQL to emulate something similar.

create or replace function generic_insert(
    p_user_name text
)
  returns boolean
  language plpgsql
as
$$
begin
    insert into users (user_name) values (p_user_name) on conflict do nothing;

    if not found then <your code> end if;

    return found;
end;
$$;

The trick here is that the found keyword returns "number of rows affected" (of the last executed query). Since do nothing did not influence any rows, found returns false, hence you'd know through this that your insert caused the do nothing portion of the on conflict command to trigger. You may of course put whatever code or insert commands you'd like inside the if statement.

1
  • Nice. The explanation is helpful. The work-around looks sound. Thank you. Apr 25 at 3:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.