How to make ON CONFLICT work for compound foreign key columns?

Question

I'm having trouble with ON CONFLICT not working for foreign key columns where the foreign key is compound. Here's an example.

create table foreign_table (
   id_a text    not null,
   id_b text    not null,
   id   integer primary key,
   constraint ft_a_b_key unique (id_a, id_b)
);

create table my_table (
   id          integer,
   ftable_id_a text,
   ftable_id_b text,
   constraint my_table_a_b_fk
      foreign key (ftable_id_a, ftable_id_b) references foreign_table (id_a, id_b)
);

Using this query:

insert into tcell_test.my_table (id, ftable_id_a, ftable_id_b) 
    values (3, 'a3', 'b3') on conflict do nothing ;

where, say, 'a3' isn't in foreign_table, I would expect the ON CONFLICT to handle the error.

Instead I get the error:

[23503] ERROR: insert or update on table "my_table" 
   violates foreign key constraint "my_table_a_b_fk" 
Detail: Key (ftable_id_a, ftable_id_b)=(a3, b3) 
   is not present in table "foreign_table".

Is there a way to correct this so ON CONFLICT handles the error?

Answer 1

I think you may be misunderstanding what a CONFLICT is. A CONFLICT is a violation of uniqueness, basically the row that is being added should not be added because another row with the same values already exists.

In your example of insert into tcell_test.my_table (id, ftable_id_a, ftable_id_b) values (3, 'a3', 'b3') on conflict do nothing;, the ON CONFLICT condition will never be reached because you have no primary key or unique constraint on my_table:

edb=# alter table my_table add primary key (ftable_id_a,ftable_id_b);
ALTER TABLE
edb=# insert into my_table (id, ftable_id_a, ftable_id_b) 
    values (3, 'a3', 'b3') on conflict do nothing ;
INSERT 0 1
edb=# insert into my_table (id, ftable_id_a, ftable_id_b) 
    values (3, 'a3', 'b3') on conflict do nothing ;
INSERT 0 0
edb=# select * from my_table ;
 id | ftable_id_a | ftable_id_b 
----+-------------+-------------
  3 | a3          | b3
(1 row)

As you can see in the example above, my second insertion into my_table did nothing because there would have been a primary key violation. If I omit the ON CONFLICT clause, I get:

edb=# insert into my_table (id, ftable_id_a, ftable_id_b) 
    values (3, 'a3', 'b3');
ERROR:  duplicate key value violates unique constraint "my_table_pkey"
DETAIL:  Key (ftable_id_a, ftable_id_b)=(a3, b3) already exists.

The message that you mentioned in you original post (ERROR: insert or update on table "my_table" violates foreign key constraint "my_table_a_b_fk") pertains to a Foreign Key violation (not a primary key/uniqueness violation). This violation occurs when there should be a row in foreign_table with id_a=a3 and id_b=b3, but there isn't. The database expects this row to exist because you have defined that my_table references foreign_table (in other words, my_table depends on foreign_table). Because the row doesn't exist in foreign_table, your insertion into my_table fails. First, insert into foreign_table, and then you can insert into my_table:

edb=# insert into my_table (id, ftable_id_a, ftable_id_b) 
    values (3, 'a3', 'b3') on conflict do nothing ;
ERROR:  insert or update on table "my_table" violates foreign key constraint "my_table_a_b_fk"
DETAIL:  Key (ftable_id_a, ftable_id_b)=(a3, b3) is not present in table "foreign_table".
edb=# insert into foreign_table values ('a3','b3',1);
INSERT 0 1
edb=# select * from foreign_table ;
 id_a | id_b | id 
------+------+----
 a3   | b3   |  1
(1 row)
edb=# insert into my_table (id, ftable_id_a, ftable_id_b) 
    values (3, 'a3', 'b3') on conflict do nothing ;
INSERT 0 1
edb=# select * from my_table ;
 id | ftable_id_a | ftable_id_b 
----+-------------+-------------
  3 | a3          | b3
(1 row)

Answer 2

If I understand correctly you want to insert the given row only if the FK constraint is satisfied - and do nothing otherwise, in particular do not raise an exception.

This is no "UPSERT", i.e. not a use case for INSERT .. ON CONFLICT DO NOTHING, which only works for violations of unique indexes or exclusion constraints. The manual:

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error.

Solution

Use an INSERT with a conditional SELECT instead. And possibly add a locking clause.

INSERT INTO my_table (id, ftable_id_a, ftable_id_b)
SELECT i.*
FROM  (VALUES (3, 'a2', 'b3')) i(id, ftable_id_a, ftable_id_b) -- provide values once
                                                               -- works for multiple rows
WHERE  EXISTS (
   SELECT FROM foreign_table f                                 -- SELECT list can be empty
   WHERE (f.id_a, f.id_b) = (i.ftable_id_a, i.ftable_id_b) 
   FOR    SHARE                                                -- weakest lock
   );

The locking clause FOR SHARE is the weakest lock and good enough for the use case. Details in the manual. Without locking, there is a tiny time frame for a race condition between looking the row(s) in the EXISTS subquery and the actual INSERT: concurrent transactions might UPDATE / DELETE the FK columns of the row in foreign_table in the meantime, which would result in an exception after all - the thing we want to avoid.

If there cannot be such concurrent writes, you can skip the locking.

Be aware of a subtle difference in type handling introduced with the subquery. With basic data types integer and text and appropriate numeric / string literal input, everything just keeps working. But you may have to cast input values explicitly for other input/target type combinations that cannot be resolved automatically. See:

• How to avoid implicit type casts in PostgreSQL?
• Casting NULL type when updating multiple rows

Related:

• How to use RETURNING with ON CONFLICT in PostgreSQL?