Question 256021 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres insert with on-conflict and foreign key check

Query:

INSERT INTO
   derived (a, b, c)
VALUES (?, ?, ?)
ON CONFLICT (a, b, c) DO UPDATE SET
   a = EXCLUDED.a,
   b = EXCLUDED.b,
   c = EXCLUDED.c
WHERE EXISTS (select a from primary where a = ?)

Please note,

  • fourth parameter is same as first parameter.
  • derived.a has foreign-key-constraint on primary.a
  • Update has to happen if there is a conflict on all 3 columns.

Unfortunately, this query is not working and I am still getting a foreign key violation when inserting with a value a which is not in primary table. Any help is appreciated.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Following did what I wanted:

    WITH data_table (a, b, c)
  AS (
    values (?, ?, ?)
  )
INSERT INTO derived (a, b, c)
SELECT dt.a, dt.b, dt.c
FROM data_table dt
WHERE EXISTS (select a from primary where a = ?)
ON CONFLICT (a, b, c) DO UPDATE SET
   a = EXCLUDED.a,
   b = EXCLUDED.b,
   c = EXCLUDED.c

  2. 0

    Your solution while giving the results you want is overly complicated as it is essentially:

    insert into
   derived (a, b, c)
values (?, ?, ?)
on conflict (a, b, c) do nothing;

    Lets analyze your solution to see why. First off your tables must look something like:

    create table primary_tbl (a   integer  generated always as identity
                                       primary key
                         ,name text not null
                          );

create table derived (a  integer references primary_tbl(a) 
                     ,b  integer 
                     ,c  integer
                     ,constraint derived_uk unique (a,b,c)
                     );

    Now the actual data types and constraint types do not matter, just that a must be the same type in each table and must be unique in the table. Then the 3 column combination must form a unique combination in derived.
    Now in order to get the conflict all 3 values, in the proper order must already exist in derived so your update operation merely sets each column to the same value it already has (yes setting a column to it’s current value does actually update the column). Finally, how about the where clause. Well if a matching value for derived.a did not exist in primary.a the row could not exist due to the it’s definition as a foreign key. Thus since the data values are the same and the where must return true the row content is exactly the same. And that is what the do nothing clause results in. So: 

    insert into derived (a, b, c)
  values (?,?,?)
  on conflict (a, b, c) 
  do nothing;

    The situation changes if there were another non-key column is involved (call it d. In this case you need to set only the value for d update phase, you do not need to update columns a,b,c – they already exit.

    insert into derived(a,b,c,d)
  values (?,?,?,?) 
 on conflict (a,b,c) 
 do update 
       set d = excluded.d;

    See demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search