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

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?

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)

StackExchange Database Administrators Q#252875, answer score: 5