cancel
Showing results for 
Search instead for 
Did you mean: 

"INSERT ON CONFLICT DO NOTHING" with postgre_fdw foreign tables

 

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

Version history
Revision #:
4 of 4
Last update:
‎03-24-2019 03:25 PM
Updated by:
 
Contributors