The statement "the ON CONFLICT DO NOTHING clause is supported, provided a unique index inference specification is omitted." in the document means that if we do not use the unique index reference( in your case id) along with the ON CONFLICT DO NOTHING, then only it will work.
Refer document on the link below for detailed information.
https://www.postgresql.org/docs/11/postgres-fdw.html
According to the document the below statement will not work as we have mentioned the unique index reference with the on conflict clause.
targetdb=# insert into test1 values (2,'Amit') on conflict(id) do nothing; ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Whereas, the below statement will work, as we do not mention the " id " which was specified as a unique index in the source table.
targetdb=# insert into test1 values (2,'Amit') on conflict do nothing; INSERT 0 0
Below is the complete scenario:
On source server
==============
foreign_db=# create table s2.t3 (id integer unique, data text);
CREATE TABLE
foreign_db=# select * from s2.t3;
id | data
----+------
10 | dfgh
15 | sw
90 | sw
foreign_db=# insert into s2.t3 values (10,'dfgh');
2019-02-02 05:29:39.748 PST [63940] ERROR: duplicate key value violates unique constraint "t3_id_key"
2019-02-02 05:29:39.748 PST [63940] DETAIL: Key (id)=(10) already exists.
2019-02-02 05:29:39.748 PST [63940] STATEMENT: insert into s2.t3 values (10,'dfgh');
ERROR: duplicate key value violates unique constraint "t3_id_key"
DETAIL: Key (id)=(10) already exists.
On target server
==============
postgres=# CREATE SERVER foreign_server
postgres-# FOREIGN DATA WRAPPER postgres_fdw
postgres-# OPTIONS (host 'localhost', port '5433', dbname 'foreign_db');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR postgres
postgres-# SERVER foreign_server
postgres-# OPTIONS (user 'postgres', password 'edb');
CREATE USER MAPPING
postgres=# CREATE FOREIGN TABLE test3
( postgres(# id integer NOT NULL,
postgres(# data text
postgres(# )
postgres-# SERVER foreign_server
postgres-# OPTIONS (schema_name 's2', table_name 't3');
CREATE FOREIGN TABLE
postgres=# select * from test3;
id | data
----+------
10 | dfgh
15 | sw
90 | sw
The following statement will work, as we do not mention the " id " which was specified as a unique index in the source table.
postgres=# insert into test3 values (90,'sw') on conflict do nothing;
INSERT 0 0
But the below statement will not work as we have mentioned the unique index reference with the on conflict clause.
postgres=# insert into test3 values (90,'sw') on conflict(id) do nothing;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
... View more