| -- |
| -- 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 |
| -- |
| create unique index op_index_key on insertconflicttest(key, fruit text_pattern_ops); |
| create unique index collation_index_key on insertconflicttest(key, fruit collate "C"); |
| create unique index both_index_key on insertconflicttest(key, fruit collate "C" text_pattern_ops); |
| create unique index both_index_expr_key on insertconflicttest(key, lower(fruit) collate "C" text_pattern_ops); |
| -- fails |
| explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key) do nothing; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit) do nothing; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| -- succeeds |
| explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do nothing; |
| QUERY PLAN |
| ------------------------------------------------------------------------------- |
| Insert on insertconflicttest |
| Conflict Resolution: NOTHING |
| Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key |
| -> Result |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit, key, fruit, key) do nothing; |
| QUERY PLAN |
| ------------------------------------------------------------------------------- |
| Insert on insertconflicttest |
| Conflict Resolution: NOTHING |
| Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key |
| -> Result |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| explain (costs off) insert into insertconflicttest 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 |
| Optimizer: Postgres query optimizer |
| (5 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 |
| ------------------------------------------------------------------------------- |
| Insert on insertconflicttest |
| Conflict Resolution: UPDATE |
| Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key |
| Conflict Filter: (SubPlan 1) |
| -> Result |
| SubPlan 1 |
| -> Result |
| Filter: (ii.key = excluded.key) |
| -> Materialize |
| -> Broadcast Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on insertconflicttest ii |
| Optimizer: Postgres query optimizer |
| (12 rows) |
| |
| -- Neither collation nor operator class specifications are required -- |
| -- supplying them merely *limits* matches to indexes with matching opclasses |
| -- used for relevant indexes |
| explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit text_pattern_ops) do nothing; |
| QUERY PLAN |
| ---------------------------------------------------------- |
| Insert on insertconflicttest |
| Conflict Resolution: NOTHING |
| Conflict Arbiter Indexes: op_index_key, both_index_key |
| -> Result |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| -- Okay, arbitrates using both index where text_pattern_ops opclass does and |
| -- does not appear. |
| explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit collate "C") do nothing; |
| QUERY PLAN |
| ----------------------------------------------------------------- |
| Insert on insertconflicttest |
| Conflict Resolution: NOTHING |
| Conflict Arbiter Indexes: collation_index_key, both_index_key |
| -> Result |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| -- Okay, but only accepts the single index where both opclass and collation are |
| -- specified |
| explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit collate "C" text_pattern_ops, key) do nothing; |
| QUERY PLAN |
| -------------------------------------------- |
| Insert on insertconflicttest |
| Conflict Resolution: NOTHING |
| Conflict Arbiter Indexes: both_index_key |
| -> Result |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| -- Okay, but only accepts the single index where both opclass and collation are |
| -- specified (plus expression variant) |
| explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) collate "C", key, key) do nothing; |
| QUERY PLAN |
| ------------------------------------------------- |
| Insert on insertconflicttest |
| Conflict Resolution: NOTHING |
| Conflict Arbiter Indexes: both_index_expr_key |
| -> Result |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| -- Attribute appears twice, while not all attributes/expressions on attributes |
| -- appearing within index definition match in terms of both opclass and |
| -- collation. |
| -- |
| -- Works because every attribute in inference specification needs to be |
| -- satisfied once or more by cataloged index attribute, and as always when an |
| -- attribute in the cataloged definition has a non-default opclass/collation, |
| -- it still satisfied some inference attribute lacking any particular |
| -- opclass/collation specification. |
| -- |
| -- The implementation is liberal in accepting inference specifications on the |
| -- assumption that multiple inferred unique indexes will prevent problematic |
| -- cases. It rolls with unique indexes where attributes redundantly appear |
| -- multiple times, too (which is not tested here). |
| explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit, key, fruit text_pattern_ops, key) do nothing; |
| QUERY PLAN |
| ---------------------------------------------------------- |
| Insert on insertconflicttest |
| Conflict Resolution: NOTHING |
| Conflict Arbiter Indexes: op_index_key, both_index_key |
| -> Result |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) collate "C" text_pattern_ops, key, key) do nothing; |
| QUERY PLAN |
| ------------------------------------------------- |
| Insert on insertconflicttest |
| Conflict Resolution: NOTHING |
| Conflict Arbiter Indexes: both_index_expr_key |
| -> Result |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| drop index op_index_key; |
| drop index collation_index_key; |
| drop index both_index_key; |
| drop index both_index_expr_key; |
| -- |
| -- Make sure that cross matching of attribute opclass/collation does not occur |
| -- |
| -- GPDB: it is not possible (yet) to express a unique index with the required characteristics |
| -- start_ignore |
| create unique index cross_match on insertconflicttest(lower(fruit) collate "C", upper(fruit) text_pattern_ops); |
| -- fails: |
| explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) text_pattern_ops, upper(fruit) collate "C") do nothing; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| -- works: |
| explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) collate "C", upper(fruit) text_pattern_ops) do nothing; |
| QUERY PLAN |
| ----------------------------------------- |
| Insert on insertconflicttest |
| Conflict Resolution: NOTHING |
| Conflict Arbiter Indexes: cross_match |
| -> Result |
| (4 rows) |
| |
| drop index cross_match; |
| -- end_ignore |
| -- |
| -- Single key tests |
| -- |
| create unique index key_index on insertconflicttest(key); |
| -- |
| -- Explain tests |
| -- |
| explain (costs off) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit; |
| QUERY PLAN |
| --------------------------------------- |
| Insert on insertconflicttest |
| Conflict Resolution: UPDATE |
| Conflict Arbiter Indexes: key_index |
| -> Result |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| -- Should display qual actually attributable to internal sequential scan: |
| explain (costs off) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Cawesh'; |
| QUERY PLAN |
| ----------------------------------------------------------------- |
| Insert on insertconflicttest |
| Conflict Resolution: UPDATE |
| Conflict Arbiter Indexes: key_index |
| Conflict Filter: (insertconflicttest.fruit <> 'Cawesh'::text) |
| -> Result |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| -- With EXCLUDED.* expression in scan node: |
| explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key) do update set fruit = excluded.fruit where excluded.fruit != 'Elderberry'; |
| QUERY PLAN |
| ----------------------------------------------------------- |
| Insert on insertconflicttest |
| Conflict Resolution: UPDATE |
| Conflict Arbiter Indexes: key_index |
| Conflict Filter: (excluded.fruit <> 'Elderberry'::text) |
| -> Result |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| -- Does the same, but JSON format shows "Conflict Arbiter Index" as JSON array: |
| explain (costs off, format json) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Lime' returning *; |
| QUERY PLAN |
| ---------------------------------------------------------------------------- |
| [ + |
| { + |
| "Plan": { + |
| "Node Type": "Gather Motion", + |
| "Senders": 1, + |
| "Receivers": 1, + |
| "Slice": 1, + |
| "Segments": 1, + |
| "Gang Type": "primary writer", + |
| "Parallel Aware": false, + |
| "Async Capable": false, + |
| "Plans": [ + |
| { + |
| "Node Type": "ModifyTable", + |
| "Operation": "Insert", + |
| "Parent Relationship": "Outer", + |
| "Slice": 1, + |
| "Segments": 1, + |
| "Gang Type": "primary writer", + |
| "Parallel Aware": false, + |
| "Async Capable": false, + |
| "Relation Name": "insertconflicttest", + |
| "Alias": "insertconflicttest", + |
| "Conflict Resolution": "UPDATE", + |
| "Conflict Arbiter Indexes": ["key_index"], + |
| "Conflict Filter": "(insertconflicttest.fruit <> 'Lime'::text)",+ |
| "Plans": [ + |
| { + |
| "Node Type": "Result", + |
| "Parent Relationship": "Outer", + |
| "Slice": 1, + |
| "Segments": 1, + |
| "Gang Type": "primary writer", + |
| "Parallel Aware": false, + |
| "Async Capable": false + |
| } + |
| ] + |
| } + |
| ] + |
| }, + |
| "Settings": { + |
| "Optimizer": "Postgres query optimizer" + |
| } + |
| } + |
| ] |
| (1 row) |
| |
| -- Fails (no unique index inference specification, required for do update variant): |
| insert into insertconflicttest values (1, 'Apple') on conflict do update set fruit = excluded.fruit; |
| ERROR: ON CONFLICT DO UPDATE requires inference specification or constraint name |
| LINE 1: ...nsert into insertconflicttest values (1, 'Apple') on conflic... |
| ^ |
| HINT: For example, ON CONFLICT (column_name). |
| -- inference succeeds: |
| insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit; |
| insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key) do update set fruit = excluded.fruit; |
| -- Succeed, since multi-assignment does not involve subquery: |
| insert into insertconflicttest |
| values (1, 'Apple'), (2, 'Orange') |
| on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key); |
| ERROR: modification of distribution columns in OnConflictUpdate is not supported |
| -- Give good diagnostic message when EXCLUDED.* spuriously referenced from |
| -- RETURNING: |
| insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit; |
| ERROR: invalid reference to FROM-clause entry for table "excluded" |
| LINE 1: ...y) do update set fruit = excluded.fruit RETURNING excluded.f... |
| ^ |
| DETAIL: There is an entry for table "excluded", but it cannot be referenced from this part of the query. |
| -- Only suggest <table>.* column when inference element misspelled: |
| insert into insertconflicttest values (1, 'Apple') on conflict (keyy) do update set fruit = excluded.fruit; |
| ERROR: column "keyy" does not exist |
| LINE 1: ...nsertconflicttest values (1, 'Apple') on conflict (keyy) do ... |
| ^ |
| HINT: Perhaps you meant to reference the column "insertconflicttest.key" or the column "excluded.key". |
| -- Have useful HINT for EXCLUDED.* RTE within UPDATE: |
| insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruitt; |
| ERROR: column excluded.fruitt does not exist |
| LINE 1: ... 'Apple') on conflict (key) do update set fruit = excluded.f... |
| ^ |
| HINT: Perhaps you meant to reference the column "excluded.fruit". |
| -- inference fails: |
| insert into insertconflicttest values (3, 'Kiwi') on conflict (key, fruit) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| insert into insertconflicttest values (4, 'Mango') on conflict (fruit, key) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| insert into insertconflicttest values (5, 'Lemon') on conflict (fruit) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| insert into insertconflicttest values (6, 'Passionfruit') on conflict (lower(fruit)) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| -- Check the target relation can be aliased |
| insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = excluded.fruit; -- ok, no reference to target table |
| insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = ict.fruit; -- ok, alias |
| insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = insertconflicttest.fruit; -- error, references aliased away name |
| ERROR: invalid reference to FROM-clause entry for table "insertconflicttest" |
| LINE 1: ...onfruit') on conflict (key) do update set fruit = insertconf... |
| ^ |
| HINT: Perhaps you meant to reference the table alias "ict". |
| drop index key_index; |
| -- |
| -- Composite key tests |
| -- |
| create unique index comp_key_index on insertconflicttest(key, fruit); |
| -- inference succeeds: |
| insert into insertconflicttest values (7, 'Raspberry') on conflict (key, fruit) do update set fruit = excluded.fruit; |
| insert into insertconflicttest values (8, 'Lime') on conflict (fruit, key) do update set fruit = excluded.fruit; |
| -- inference fails: |
| insert into insertconflicttest values (9, 'Banana') on conflict (key) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| insert into insertconflicttest values (10, 'Blueberry') on conflict (key, key, key) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| insert into insertconflicttest values (11, 'Cherry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| insert into insertconflicttest values (12, 'Date') on conflict (lower(fruit), key) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| drop index comp_key_index; |
| -- |
| -- Partial index tests, no inference predicate specified |
| -- |
| create unique index part_comp_key_index on insertconflicttest(key, fruit) where key < 5; |
| create unique index expr_part_comp_key_index on insertconflicttest(key, lower(fruit)) where key < 5; |
| -- inference fails: |
| insert into insertconflicttest values (13, 'Grape') on conflict (key, fruit) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| insert into insertconflicttest values (14, 'Raisin') on conflict (fruit, key) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| insert into insertconflicttest values (15, 'Cranberry') on conflict (key) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| insert into insertconflicttest values (16, 'Melon') on conflict (key, key, key) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| insert into insertconflicttest values (17, 'Mulberry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| insert into insertconflicttest values (18, 'Pineapple') on conflict (lower(fruit), key) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| drop index part_comp_key_index; |
| drop index expr_part_comp_key_index; |
| -- |
| -- Expression index tests |
| -- |
| -- start_ignore |
| create unique index expr_key_index on insertconflicttest(lower(fruit)); |
| -- inference succeeds: |
| insert into insertconflicttest values (20, 'Quince') on conflict (lower(fruit)) do update set fruit = excluded.fruit; |
| insert into insertconflicttest values (21, 'Pomegranate') on conflict (lower(fruit), lower(fruit)) do update set fruit = excluded.fruit; |
| -- inference fails: |
| insert into insertconflicttest values (22, 'Apricot') on conflict (upper(fruit)) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| drop index expr_key_index; |
| -- end_ignore |
| -- |
| -- Expression index tests (with regular column) |
| -- |
| create unique index expr_comp_key_index on insertconflicttest(key, lower(fruit)); |
| create unique index tricky_expr_comp_key_index on insertconflicttest(key, lower(fruit), upper(fruit)); |
| -- inference succeeds: |
| insert into insertconflicttest values (24, 'Plum') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit; |
| insert into insertconflicttest values (25, 'Peach') on conflict (lower(fruit), key) do update set fruit = excluded.fruit; |
| -- Should not infer "tricky_expr_comp_key_index" index: |
| explain (costs off) insert into insertconflicttest values (26, 'Fig') on conflict (lower(fruit), key, lower(fruit), key) do update set fruit = excluded.fruit; |
| QUERY PLAN |
| ------------------------------------------------- |
| Insert on insertconflicttest |
| Conflict Resolution: UPDATE |
| Conflict Arbiter Indexes: expr_comp_key_index |
| -> Result |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| -- inference fails: |
| insert into insertconflicttest values (27, 'Prune') on conflict (key, upper(fruit)) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| insert into insertconflicttest values (28, 'Redcurrant') on conflict (fruit, key) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| insert into insertconflicttest values (29, 'Nectarine') on conflict (key) do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| drop index expr_comp_key_index; |
| drop index tricky_expr_comp_key_index; |
| -- |
| -- Non-spurious duplicate violation tests |
| -- |
| -- start_ignore |
| create unique index key_index on insertconflicttest(key); |
| create unique index fruit_index on insertconflicttest(fruit); |
| -- succeeds, since UPDATE happens to update "fruit" to existing value: |
| insert into insertconflicttest values (26, 'Fig') on conflict (key) do update set fruit = excluded.fruit; |
| -- fails, since UPDATE is to row with key value 26, and we're updating "fruit" |
| -- to a value that happens to exist in another row ('peach'): |
| insert into insertconflicttest values (26, 'Peach') on conflict (key) do update set fruit = excluded.fruit; |
| ERROR: duplicate key value violates unique constraint "fruit_index" |
| DETAIL: Key (fruit)=(Peach) already exists. |
| -- succeeds, since "key" isn't repeated/referenced in UPDATE, and "fruit" |
| -- arbitrates that statement updates existing "Fig" row: |
| insert into insertconflicttest values (25, 'Fig') on conflict (fruit) do update set fruit = excluded.fruit; |
| drop index key_index; |
| drop index fruit_index; |
| -- end_ignore |
| -- |
| -- Test partial unique index inference |
| -- |
| create unique index partial_key_index on insertconflicttest(key) where fruit like '%berry'; |
| -- 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 |
| insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' or fruit = 'consequential' do nothing; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) where fruit like '%berry' do update set fruit = excluded.fruit; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| drop index partial_key_index; |
| -- |
| -- Test that wholerow references to ON CONFLICT's EXCLUDED work |
| -- |
| create unique index plain on insertconflicttest(key); |
| -- Succeeds, updates existing row: |
| insert into insertconflicttest as i values (23, 'Jackfruit') on conflict (key) do update set fruit = excluded.fruit |
| where i.* != excluded.* returning *; |
| key | fruit |
| -----+----------- |
| 23 | Jackfruit |
| (1 row) |
| |
| -- No update this time, though: |
| insert into insertconflicttest as i values (23, 'Jackfruit') on conflict (key) do update set fruit = excluded.fruit |
| where i.* != excluded.* returning *; |
| key | fruit |
| -----+------- |
| (0 rows) |
| |
| -- Predicate changed to require match rather than non-match, so updates once more: |
| insert into insertconflicttest as i values (23, 'Jackfruit') on conflict (key) do update set fruit = excluded.fruit |
| where i.* = excluded.* returning *; |
| key | fruit |
| -----+----------- |
| 23 | Jackfruit |
| (1 row) |
| |
| -- Assign: |
| insert into insertconflicttest as i values (23, 'Avocado') on conflict (key) do update set fruit = excluded.*::text |
| returning *; |
| key | fruit |
| -----+-------------- |
| 23 | (23,Avocado) |
| (1 row) |
| |
| -- deparse whole row var in WHERE and SET clauses: |
| explain (costs off) insert into insertconflicttest as i values (23, 'Avocado') on conflict (key) do update set fruit = excluded.fruit where excluded.* is null; |
| QUERY PLAN |
| ----------------------------------------- |
| Insert on insertconflicttest i |
| Conflict Resolution: UPDATE |
| Conflict Arbiter Indexes: plain |
| Conflict Filter: (excluded.* IS NULL) |
| -> Result |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| explain (costs off) insert into insertconflicttest as i values (23, 'Avocado') on conflict (key) do update set fruit = excluded.*::text; |
| QUERY PLAN |
| ------------------------------------- |
| Insert on insertconflicttest i |
| Conflict Resolution: UPDATE |
| Conflict Arbiter Indexes: plain |
| -> Result |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| drop index plain; |
| -- Cleanup |
| drop view insertconflictview; |
| drop table insertconflicttest; |
| -- |
| -- Verify that EXCLUDED does not allow system column references. These |
| -- do not make sense because EXCLUDED isn't an already stored tuple |
| -- (and thus doesn't have a ctid etc). |
| -- |
| create table syscolconflicttest(key int4, data text); |
| insert into syscolconflicttest values (1); |
| insert into syscolconflicttest values (1) on conflict (key) do update set data = excluded.ctid::text; |
| ERROR: column excluded.ctid does not exist |
| LINE 1: ...values (1) on conflict (key) do update set data = excluded.c... |
| ^ |
| drop table syscolconflicttest; |
| -- |
| -- Previous tests all managed to not test any expressions requiring |
| -- planner preprocessing ... |
| -- |
| -- GPDB: it is not possible (yet) to express a unique index with expressions |
| -- start_ignore |
| create table insertconflict (a bigint, b bigint); |
| create unique index insertconflicti1 on insertconflict(coalesce(a, 0)); |
| create unique index insertconflicti2 on insertconflict(b) |
| where coalesce(a, 1) > 0; |
| insert into insertconflict values (1, 2) |
| on conflict (coalesce(a, 0)) do nothing; |
| insert into insertconflict values (1, 2) |
| on conflict (b) where coalesce(a, 1) > 0 do nothing; |
| insert into insertconflict values (1, 2) |
| on conflict (b) where coalesce(a, 1) > 1 do nothing; |
| drop table insertconflict; |
| -- end_ignore |
| -- |
| -- test insertion through view |
| -- |
| create table insertconflict (f1 int primary key, f2 text); |
| create view insertconflictv as |
| select * from insertconflict with cascaded check option; |
| insert into insertconflictv values (1,'foo') |
| on conflict (f1) do update set f2 = excluded.f2; |
| select * from insertconflict; |
| f1 | f2 |
| ----+----- |
| 1 | foo |
| (1 row) |
| |
| insert into insertconflictv values (1,'bar') |
| on conflict (f1) do update set f2 = excluded.f2; |
| select * from insertconflict; |
| f1 | f2 |
| ----+----- |
| 1 | bar |
| (1 row) |
| |
| drop view insertconflictv; |
| drop table insertconflict; |
| -- ****************************************************************** |
| -- * * |
| -- * Test inheritance (example taken from tutorial) * |
| -- * * |
| -- ****************************************************************** |
| create table cities ( |
| name text, |
| population float8, |
| altitude int -- (in ft) |
| ); |
| create table capitals ( |
| state char(2) |
| ) inherits (cities); |
| -- Create unique indexes. Due to a general limitation of inheritance, |
| -- uniqueness is only enforced per-relation. Unique index inference |
| -- specification will do the right thing, though. |
| create unique index cities_names_unique on cities (name); |
| create unique index capitals_names_unique on capitals (name); |
| -- prepopulate the tables. |
| insert into cities values ('San Francisco', 7.24E+5, 63); |
| insert into cities values ('Las Vegas', 2.583E+5, 2174); |
| insert into cities values ('Mariposa', 1200, 1953); |
| insert into capitals values ('Sacramento', 3.694E+5, 30, 'CA'); |
| insert into capitals values ('Madison', 1.913E+5, 845, 'WI'); |
| -- Tests proper for inheritance: |
| select * from capitals; |
| name | population | altitude | state |
| ------------+------------+----------+------- |
| Sacramento | 369400 | 30 | CA |
| Madison | 191300 | 845 | WI |
| (2 rows) |
| |
| -- Succeeds: |
| insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict do nothing; |
| insert into capitals values ('Sacramento', 4664.E+5, 30, 'CA') on conflict (name) do update set population = excluded.population; |
| -- Wrong "Sacramento", so do nothing: |
| insert into capitals values ('Sacramento', 50, 2267, 'NE') on conflict (name) do nothing; |
| select * from capitals; |
| name | population | altitude | state |
| ------------+------------+----------+------- |
| Madison | 191300 | 845 | WI |
| Sacramento | 466400000 | 30 | CA |
| (2 rows) |
| |
| insert into cities values ('Las Vegas', 5.83E+5, 2001) on conflict (name) do update set population = excluded.population, altitude = excluded.altitude; |
| select tableoid::regclass, * from cities; |
| tableoid | name | population | altitude |
| ----------+---------------+------------+---------- |
| cities | San Francisco | 724000 | 63 |
| cities | Mariposa | 1200 | 1953 |
| cities | Las Vegas | 583000 | 2001 |
| capitals | Madison | 191300 | 845 |
| capitals | Sacramento | 466400000 | 30 |
| (5 rows) |
| |
| insert into capitals values ('Las Vegas', 5.83E+5, 2222, 'NV') on conflict (name) do update set population = excluded.population; |
| -- Capitals will contain new capital, Las Vegas: |
| select * from capitals; |
| name | population | altitude | state |
| ------------+------------+----------+------- |
| Madison | 191300 | 845 | WI |
| Sacramento | 466400000 | 30 | CA |
| Las Vegas | 583000 | 2222 | NV |
| (3 rows) |
| |
| -- Cities contains two instances of "Las Vegas", since unique constraints don't |
| -- work across inheritance: |
| select tableoid::regclass, * from cities; |
| tableoid | name | population | altitude |
| ----------+---------------+------------+---------- |
| cities | San Francisco | 724000 | 63 |
| cities | Mariposa | 1200 | 1953 |
| cities | Las Vegas | 583000 | 2001 |
| capitals | Madison | 191300 | 845 |
| capitals | Sacramento | 466400000 | 30 |
| capitals | Las Vegas | 583000 | 2222 |
| (6 rows) |
| |
| -- This only affects "cities" version of "Las Vegas": |
| insert into cities values ('Las Vegas', 5.86E+5, 2223) on conflict (name) do update set population = excluded.population, altitude = excluded.altitude; |
| select tableoid::regclass, * from cities; |
| tableoid | name | population | altitude |
| ----------+---------------+------------+---------- |
| cities | San Francisco | 724000 | 63 |
| cities | Mariposa | 1200 | 1953 |
| cities | Las Vegas | 586000 | 2223 |
| capitals | Madison | 191300 | 845 |
| capitals | Sacramento | 466400000 | 30 |
| capitals | Las Vegas | 583000 | 2222 |
| (6 rows) |
| |
| -- clean up |
| drop table capitals; |
| drop table cities; |
| -- Make sure a table named excluded is handled properly |
| create table excluded(key int primary key, data text); |
| insert into excluded values(1, '1'); |
| -- error, ambiguous |
| insert into excluded values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; |
| ERROR: table reference "excluded" is ambiguous |
| LINE 1: ...es(1, '2') on conflict (key) do update set data = excluded.d... |
| ^ |
| -- ok, aliased |
| insert into excluded AS target values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; |
| key | data |
| -----+------ |
| 1 | 2 |
| (1 row) |
| |
| -- ok, aliased |
| insert into excluded AS target values(1, '2') on conflict (key) do update set data = target.data RETURNING *; |
| key | data |
| -----+------ |
| 1 | 2 |
| (1 row) |
| |
| -- make sure excluded isn't a problem in returning clause |
| insert into excluded values(1, '2') on conflict (key) do update set data = 3 RETURNING excluded.*; |
| key | data |
| -----+------ |
| 1 | 3 |
| (1 row) |
| |
| -- clean up |
| drop table excluded; |
| -- check that references to columns after dropped columns are handled correctly |
| create table dropcol(key int primary key, drop1 int, keep1 text, drop2 numeric, keep2 float); |
| insert into dropcol(key, drop1, keep1, drop2, keep2) values(1, 1, '1', '1', 1); |
| -- set using excluded |
| insert into dropcol(key, drop1, keep1, drop2, keep2) values(1, 2, '2', '2', 2) on conflict(key) |
| do update set drop1 = excluded.drop1, keep1 = excluded.keep1, drop2 = excluded.drop2, keep2 = excluded.keep2 |
| where excluded.drop1 is not null and excluded.keep1 is not null and excluded.drop2 is not null and excluded.keep2 is not null |
| and dropcol.drop1 is not null and dropcol.keep1 is not null and dropcol.drop2 is not null and dropcol.keep2 is not null |
| returning *; |
| key | drop1 | keep1 | drop2 | keep2 |
| -----+-------+-------+-------+------- |
| 1 | 2 | 2 | 2 | 2 |
| (1 row) |
| |
| ; |
| -- set using existing table |
| insert into dropcol(key, drop1, keep1, drop2, keep2) values(1, 3, '3', '3', 3) on conflict(key) |
| do update set drop1 = dropcol.drop1, keep1 = dropcol.keep1, drop2 = dropcol.drop2, keep2 = dropcol.keep2 |
| returning *; |
| key | drop1 | keep1 | drop2 | keep2 |
| -----+-------+-------+-------+------- |
| 1 | 2 | 2 | 2 | 2 |
| (1 row) |
| |
| ; |
| alter table dropcol drop column drop1, drop column drop2; |
| -- set using excluded |
| insert into dropcol(key, keep1, keep2) values(1, '4', 4) on conflict(key) |
| do update set keep1 = excluded.keep1, keep2 = excluded.keep2 |
| where excluded.keep1 is not null and excluded.keep2 is not null |
| and dropcol.keep1 is not null and dropcol.keep2 is not null |
| returning *; |
| key | keep1 | keep2 |
| -----+-------+------- |
| 1 | 4 | 4 |
| (1 row) |
| |
| ; |
| -- set using existing table |
| insert into dropcol(key, keep1, keep2) values(1, '5', 5) on conflict(key) |
| do update set keep1 = dropcol.keep1, keep2 = dropcol.keep2 |
| returning *; |
| key | keep1 | keep2 |
| -----+-------+------- |
| 1 | 4 | 4 |
| (1 row) |
| |
| ; |
| DROP TABLE dropcol; |
| -- check handling of regular btree constraint along with gist constraint |
| -- GPDB: does not support exclusion constraints |
| -- start_ignore |
| create table twoconstraints (f1 int unique, f2 box, |
| exclude using gist(f2 with &&)); |
| insert into twoconstraints values(1, '((0,0),(1,1))'); |
| insert into twoconstraints values(1, '((2,2),(3,3))'); -- fail on f1 |
| ERROR: duplicate key value violates unique constraint "twoconstraints_f1_key" |
| DETAIL: Key (f1)=(1) already exists. |
| insert into twoconstraints values(2, '((0,0),(1,2))'); -- fail on f2 |
| ERROR: conflicting key value violates exclusion constraint "twoconstraints_f2_excl" |
| DETAIL: Key (f2)=((1,2),(0,0)) conflicts with existing key (f2)=((1,1),(0,0)). |
| insert into twoconstraints values(2, '((0,0),(1,2))') |
| on conflict on constraint twoconstraints_f1_key do nothing; -- fail on f2 |
| ERROR: conflicting key value violates exclusion constraint "twoconstraints_f2_excl" |
| DETAIL: Key (f2)=((1,2),(0,0)) conflicts with existing key (f2)=((1,1),(0,0)). |
| insert into twoconstraints values(2, '((0,0),(1,2))') |
| on conflict on constraint twoconstraints_f2_excl do nothing; -- do nothing |
| select * from twoconstraints; |
| f1 | f2 |
| ----+------------- |
| 1 | (1,1),(0,0) |
| (1 row) |
| |
| drop table twoconstraints; |
| -- end_ignore |
| -- check handling of self-conflicts at various isolation levels |
| create table selfconflict (f1 int primary key, f2 int); |
| begin transaction isolation level read committed; |
| insert into selfconflict values (1,1), (1,2) on conflict do nothing; |
| commit; |
| begin transaction isolation level repeatable read; |
| insert into selfconflict values (2,1), (2,2) on conflict do nothing; |
| commit; |
| begin transaction isolation level serializable; |
| insert into selfconflict values (3,1), (3,2) on conflict do nothing; |
| commit; |
| begin transaction isolation level read committed; |
| insert into selfconflict values (4,1), (4,2) on conflict(f1) do update set f2 = 0; |
| ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time |
| HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. |
| commit; |
| begin transaction isolation level repeatable read; |
| insert into selfconflict values (5,1), (5,2) on conflict(f1) do update set f2 = 0; |
| ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time |
| HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. |
| commit; |
| begin transaction isolation level serializable; |
| insert into selfconflict values (6,1), (6,2) on conflict(f1) do update set f2 = 0; |
| ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time |
| HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. |
| commit; |
| select * from selfconflict; |
| f1 | f2 |
| ----+---- |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| (3 rows) |
| |
| drop table selfconflict; |
| -- check ON CONFLICT handling with partitioned tables |
| create table parted_conflict_test (a int unique, b char) partition by list (a); |
| create table parted_conflict_test_1 partition of parted_conflict_test for values in (1, 2); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| -- no indexes required here |
| insert into parted_conflict_test values (1, 'a') on conflict do nothing; |
| -- index on a required, which does exist in parent |
| insert into parted_conflict_test values (1, 'a') on conflict (a) do nothing; |
| insert into parted_conflict_test values (1, 'a') on conflict (a) do update set b = excluded.b; |
| -- targeting partition directly will work |
| insert into parted_conflict_test_1 values (1, 'a') on conflict (a) do nothing; |
| insert into parted_conflict_test_1 values (1, 'b') on conflict (a) do update set b = excluded.b; |
| -- start_ignore |
| -- Cloudberry could not update the distribution column on conflict |
| -- index on b required, which doesn't exist in parent |
| insert into parted_conflict_test values (2, 'b') on conflict (b) do update set a = excluded.a; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| -- targeting partition directly will work |
| insert into parted_conflict_test_1 values (2, 'b') on conflict (b) do update set a = excluded.a; |
| -- should see (2, 'b') |
| select * from parted_conflict_test order by a; |
| a | b |
| ---+--- |
| 2 | b |
| (1 row) |
| |
| -- end_ignore |
| -- now check that DO UPDATE works correctly for target partition with |
| -- different attribute numbers |
| create table parted_conflict_test_2 (b char, a int unique); |
| alter table parted_conflict_test attach partition parted_conflict_test_2 for values in (3); |
| truncate parted_conflict_test; |
| insert into parted_conflict_test values (3, 'a') on conflict (a) do update set b = excluded.b; |
| insert into parted_conflict_test values (3, 'b') on conflict (a) do update set b = excluded.b; |
| -- should see (3, 'b') |
| select * from parted_conflict_test order by a; |
| a | b |
| ---+--- |
| 3 | b |
| (1 row) |
| |
| -- case where parent will have a dropped column, but the partition won't |
| alter table parted_conflict_test drop b, add b char; |
| create table parted_conflict_test_3 partition of parted_conflict_test for values in (4); |
| truncate parted_conflict_test; |
| insert into parted_conflict_test (a, b) values (4, 'a') on conflict (a) do update set b = excluded.b; |
| insert into parted_conflict_test (a, b) values (4, 'b') on conflict (a) do update set b = excluded.b where parted_conflict_test.b = 'a'; |
| -- should see (4, 'b') |
| select * from parted_conflict_test order by a; |
| a | b |
| ---+--- |
| 4 | b |
| (1 row) |
| |
| -- case with multi-level partitioning |
| create table parted_conflict_test_4 partition of parted_conflict_test for values in (5) partition by list (a); |
| create table parted_conflict_test_4_1 partition of parted_conflict_test_4 for values in (5); |
| truncate parted_conflict_test; |
| insert into parted_conflict_test (a, b) values (5, 'a') on conflict (a) do update set b = excluded.b; |
| insert into parted_conflict_test (a, b) values (5, 'b') on conflict (a) do update set b = excluded.b where parted_conflict_test.b = 'a'; |
| -- should see (5, 'b') |
| select * from parted_conflict_test order by a; |
| a | b |
| ---+--- |
| 5 | b |
| (1 row) |
| |
| -- test with multiple rows |
| truncate parted_conflict_test; |
| insert into parted_conflict_test (a, b) values (1, 'a'), (2, 'a'), (4, 'a') on conflict (a) do update set b = excluded.b where excluded.b = 'b'; |
| insert into parted_conflict_test (a, b) values (1, 'b'), (2, 'c'), (4, 'b') on conflict (a) do update set b = excluded.b where excluded.b = 'b'; |
| -- should see (1, 'b'), (2, 'a'), (4, 'b') |
| select * from parted_conflict_test order by a; |
| a | b |
| ---+--- |
| 1 | b |
| 2 | a |
| 4 | b |
| (3 rows) |
| |
| drop table parted_conflict_test; |
| -- test behavior of inserting a conflicting tuple into an intermediate |
| -- partitioning level |
| create table parted_conflict (a int primary key, b text) partition by range (a); |
| create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000) partition by range (a); |
| create table parted_conflict_1_1 partition of parted_conflict_1 for values from (0) to (500); |
| insert into parted_conflict values (40, 'forty'); |
| insert into parted_conflict_1 values (40, 'cuarenta') |
| on conflict (a) do update set b = excluded.b; |
| drop table parted_conflict; |
| -- same thing, but this time try to use an index that's created not in the |
| -- partition |
| create table parted_conflict (a int, b text) partition by range (a); |
| create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000) partition by range (a); |
| create table parted_conflict_1_1 partition of parted_conflict_1 for values from (0) to (500); |
| create unique index on only parted_conflict_1 (a); |
| create unique index on only parted_conflict (a); |
| alter index parted_conflict_a_idx attach partition parted_conflict_1_a_idx; |
| insert into parted_conflict values (40, 'forty'); |
| insert into parted_conflict_1 values (40, 'cuarenta') |
| on conflict (a) do update set b = excluded.b; |
| ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |
| drop table parted_conflict; |
| -- test whole-row Vars in ON CONFLICT expressions |
| create table parted_conflict (a int, b text, c int) partition by range (a); |
| create table parted_conflict_1 (drp text, c int, a int, b text); |
| alter table parted_conflict_1 set distributed by (a); |
| alter table parted_conflict_1 drop column drp; |
| create unique index on parted_conflict (a, b); |
| alter table parted_conflict attach partition parted_conflict_1 for values from (0) to (1000); |
| truncate parted_conflict; |
| insert into parted_conflict values (50, 'cincuenta', 1); |
| -- start_ignore |
| -- Cloudberry could not update the distribution column on conflict |
| insert into parted_conflict values (50, 'cincuenta', 2) |
| on conflict (a, b) do update set (a, b, c) = row(excluded.*) |
| where parted_conflict = (50, text 'cincuenta', 1) and |
| excluded = (50, text 'cincuenta', 2); |
| -- should see (50, 'cincuenta', 2) |
| select * from parted_conflict order by a; |
| a | b | c |
| ----+-----------+--- |
| 50 | cincuenta | 2 |
| (1 row) |
| |
| -- end_ignore |
| -- test with statement level triggers |
| create or replace function parted_conflict_update_func() returns trigger as $$ |
| declare |
| r record; |
| begin |
| for r in select * from inserted loop |
| raise notice 'a = %, b = %, c = %', r.a, r.b, r.c; |
| end loop; |
| return new; |
| end; |
| $$ language plpgsql; |
| create trigger parted_conflict_update |
| after update on parted_conflict |
| referencing new table as inserted |
| for each statement |
| execute procedure parted_conflict_update_func(); |
| ERROR: Triggers for statements are not yet supported |
| truncate parted_conflict; |
| insert into parted_conflict values (0, 'cero', 1); |
| -- Cloudberry: this won't trigger, because the INSERT/UPDATE happens on the QEs, |
| -- but FOR STATEMENT triggers could not be triggered on a QE |
| insert into parted_conflict values(0, 'cero', 1) |
| on conflict (a,b) do update set c = parted_conflict.c + 1; |
| drop table parted_conflict; |
| drop function parted_conflict_update_func(); |
| -- check that modification of replicated tables containing volatile functions is not supported. |
| create table rpt_volatile(i int unique) distributed replicated; |
| insert into rpt_volatile select i from generate_series(10,20)i; |
| -- this should fail |
| insert into rpt_volatile as m select x from generate_series(5, 15)x |
| on conflict (i) do update |
| set i = m.i*20 + 5 * random(); |
| ERROR: modification of replicated tables containing volatile functions in OnConflictUpdate is not supported |
| insert into rpt_volatile as m select x from generate_series(5, 15)x |
| on conflict (i) do update |
| set i = m.i + 20 where m.i > 12 * random(); |
| ERROR: modification of replicated tables containing volatile functions in OnConflictUpdate is not supported |
| -- this should work |
| insert into rpt_volatile as m select x from generate_series(5, 15)x |
| on conflict (i) do update |
| set i = m.i + 20; |
| drop table rpt_volatile; |
