diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/insert_conflict.out | 13 | ||||
| -rw-r--r-- | src/test/regress/sql/insert_conflict.sql | 6 |
2 files changed, 19 insertions, 0 deletions
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out index 9e9e3bd00cd..15fc426d661 100644 --- a/src/test/regress/expected/insert_conflict.out +++ b/src/test/regress/expected/insert_conflict.out @@ -2,6 +2,8 @@ -- insert...on conflict do unique index inference -- create table insertconflicttest(key int4, fruit text); +-- These things should work through a view, as well +create view insertconflictview as select * from insertconflicttest; -- -- Test unique index inference with operator class specifications and -- named collations @@ -43,6 +45,15 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con -> Result (4 rows) +explain (costs off) insert into insertconflictview values(0, 'Crowberry') on conflict (lower(fruit), key, lower(fruit), key) do nothing; + QUERY PLAN +------------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: NOTHING + Conflict Arbiter Indexes: both_index_expr_key + -> Result +(4 rows) + explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do update set fruit = excluded.fruit where exists (select 1 from insertconflicttest ii where ii.key = excluded.key); QUERY PLAN @@ -374,6 +385,7 @@ create unique index partial_key_index on insertconflicttest(key) where fruit lik -- Succeeds insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' do update set fruit = excluded.fruit; insert into insertconflicttest as t values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and t.fruit = 'inconsequential' do nothing; +insert into insertconflictview as t values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and t.fruit = 'inconsequential' do nothing; -- fails insert into insertconflicttest values (23, 'Blackberry') on conflict (key) do update set fruit = excluded.fruit; ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification @@ -439,6 +451,7 @@ explain (costs off) insert into insertconflicttest as i values (23, 'Avocado') o drop index plain; -- Cleanup +drop view insertconflictview; drop table insertconflicttest; -- -- Verify that EXCLUDED does not allow system column references. These diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql index 23d5778b821..144c3470caa 100644 --- a/src/test/regress/sql/insert_conflict.sql +++ b/src/test/regress/sql/insert_conflict.sql @@ -3,6 +3,9 @@ -- create table insertconflicttest(key int4, fruit text); +-- These things should work through a view, as well +create view insertconflictview as select * from insertconflicttest; + -- -- Test unique index inference with operator class specifications and -- named collations @@ -20,6 +23,7 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do nothing; explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit, key, fruit, key) do nothing; explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit), key, lower(fruit), key) do nothing; +explain (costs off) insert into insertconflictview values(0, 'Crowberry') on conflict (lower(fruit), key, lower(fruit), key) do nothing; explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do update set fruit = excluded.fruit where exists (select 1 from insertconflicttest ii where ii.key = excluded.key); -- Neither collation nor operator class specifications are required -- @@ -215,6 +219,7 @@ create unique index partial_key_index on insertconflicttest(key) where fruit lik -- Succeeds insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' do update set fruit = excluded.fruit; insert into insertconflicttest as t values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and t.fruit = 'inconsequential' do nothing; +insert into insertconflictview as t values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and t.fruit = 'inconsequential' do nothing; -- fails insert into insertconflicttest values (23, 'Blackberry') on conflict (key) do update set fruit = excluded.fruit; @@ -247,6 +252,7 @@ explain (costs off) insert into insertconflicttest as i values (23, 'Avocado') o drop index plain; -- Cleanup +drop view insertconflictview; drop table insertconflicttest; |