PostgreSQL 10.0 preview 功能增强 - 支持分区表ON CONFLICT .. DO NOTHING
背景
目前支持分区表的ON CONFLICT .. DO NOTHING ，暂时还不支持ON CONFLICT .. DO UPDATE
Allow ON CONFLICT .. DO NOTHING on a partitioned table.
ON CONFLICT .. DO UPDATE still doesn't work, for lack of a way of
enforcing uniqueness across partitions, but we can still allow this
case.
Amit Langote, per discussion with Peter Geoghegan. Additional
wordsmithing by me.
Discussion: http://postgr.es/m/CAA-aLv7Z4uygtq-Q5CvDi9Y=VZxUyEnuWjL=EwCfOof=L04hgg@mail.gmail.com
测试
+-- check that the following works:
+-- insert into partitioned_table on conflict do nothing
+create table parted_conflict_test (a int, b char) partition by list (a);
+create table parted_conflict_test_1 partition of parted_conflict_test for values in (1);
+insert into parted_conflict_test values (1, 'a') on conflict do nothing;
+insert into parted_conflict_test values (1, 'a') on conflict do nothing;
+-- however, on conflict do update not supported yet
+insert into parted_conflict_test values (1) on conflict (a) do update set b = excluded.b where excluded.a = 1;
+ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
+drop table parted_conflict_test, parted_conflict_test_1;
参考
https://postgr.es/m/20161206034955.bh33paeralxbtluv@alap3.anarazel.de
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b8d7f053c5c2bf2a7e8734fe3327f6a8bc711755
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2f0903ea196503fc8af373a9de46b1e01a23508c
https://postgr.es/m/26088.1490315792@sss.pgh.pa.us