1-- 2-- insert...on conflict do unique index inference 3-- 4create table insertconflicttest(key int4, fruit text); 5-- 6-- Test unique index inference with operator class specifications and 7-- named collations 8-- 9create unique index op_index_key on insertconflicttest(key, fruit text_pattern_ops); 10create unique index collation_index_key on insertconflicttest(key, fruit collate "C"); 11create unique index both_index_key on insertconflicttest(key, fruit collate "C" text_pattern_ops); 12create unique index both_index_expr_key on insertconflicttest(key, lower(fruit) collate "C" text_pattern_ops); 13-- fails 14explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key) do nothing; 15ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 16explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit) do nothing; 17ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 18-- succeeds 19explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do nothing; 20 QUERY PLAN 21------------------------------------------------------------------------------- 22 Insert on insertconflicttest 23 Conflict Resolution: NOTHING 24 Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key 25 -> Result 26(4 rows) 27 28explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit, key, fruit, key) do nothing; 29 QUERY PLAN 30------------------------------------------------------------------------------- 31 Insert on insertconflicttest 32 Conflict Resolution: NOTHING 33 Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key 34 -> Result 35(4 rows) 36 37explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit), key, lower(fruit), key) do nothing; 38 QUERY PLAN 39------------------------------------------------- 40 Insert on insertconflicttest 41 Conflict Resolution: NOTHING 42 Conflict Arbiter Indexes: both_index_expr_key 43 -> Result 44(4 rows) 45 46explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do update set fruit = excluded.fruit 47 where exists (select 1 from insertconflicttest ii where ii.key = excluded.key); 48 QUERY PLAN 49------------------------------------------------------------------------------- 50 Insert on insertconflicttest 51 Conflict Resolution: UPDATE 52 Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key 53 Conflict Filter: (alternatives: SubPlan 1 or hashed SubPlan 2) 54 -> Result 55 SubPlan 1 56 -> Index Only Scan using both_index_expr_key on insertconflicttest ii 57 Index Cond: (key = excluded.key) 58 SubPlan 2 59 -> Seq Scan on insertconflicttest ii_1 60(10 rows) 61 62-- Neither collation nor operator class specifications are required -- 63-- supplying them merely *limits* matches to indexes with matching opclasses 64-- used for relevant indexes 65explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit text_pattern_ops) do nothing; 66 QUERY PLAN 67---------------------------------------------------------- 68 Insert on insertconflicttest 69 Conflict Resolution: NOTHING 70 Conflict Arbiter Indexes: op_index_key, both_index_key 71 -> Result 72(4 rows) 73 74-- Okay, arbitrates using both index where text_pattern_ops opclass does and 75-- does not appear. 76explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit collate "C") do nothing; 77 QUERY PLAN 78----------------------------------------------------------------- 79 Insert on insertconflicttest 80 Conflict Resolution: NOTHING 81 Conflict Arbiter Indexes: collation_index_key, both_index_key 82 -> Result 83(4 rows) 84 85-- Okay, but only accepts the single index where both opclass and collation are 86-- specified 87explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit collate "C" text_pattern_ops, key) do nothing; 88 QUERY PLAN 89-------------------------------------------- 90 Insert on insertconflicttest 91 Conflict Resolution: NOTHING 92 Conflict Arbiter Indexes: both_index_key 93 -> Result 94(4 rows) 95 96-- Okay, but only accepts the single index where both opclass and collation are 97-- specified (plus expression variant) 98explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) collate "C", key, key) do nothing; 99 QUERY PLAN 100------------------------------------------------- 101 Insert on insertconflicttest 102 Conflict Resolution: NOTHING 103 Conflict Arbiter Indexes: both_index_expr_key 104 -> Result 105(4 rows) 106 107-- Attribute appears twice, while not all attributes/expressions on attributes 108-- appearing within index definition match in terms of both opclass and 109-- collation. 110-- 111-- Works because every attribute in inference specification needs to be 112-- satisfied once or more by cataloged index attribute, and as always when an 113-- attribute in the cataloged definition has a non-default opclass/collation, 114-- it still satisfied some inference attribute lacking any particular 115-- opclass/collation specification. 116-- 117-- The implementation is liberal in accepting inference specifications on the 118-- assumption that multiple inferred unique indexes will prevent problematic 119-- cases. It rolls with unique indexes where attributes redundantly appear 120-- multiple times, too (which is not tested here). 121explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit, key, fruit text_pattern_ops, key) do nothing; 122 QUERY PLAN 123---------------------------------------------------------- 124 Insert on insertconflicttest 125 Conflict Resolution: NOTHING 126 Conflict Arbiter Indexes: op_index_key, both_index_key 127 -> Result 128(4 rows) 129 130explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) collate "C" text_pattern_ops, key, key) do nothing; 131 QUERY PLAN 132------------------------------------------------- 133 Insert on insertconflicttest 134 Conflict Resolution: NOTHING 135 Conflict Arbiter Indexes: both_index_expr_key 136 -> Result 137(4 rows) 138 139drop index op_index_key; 140drop index collation_index_key; 141drop index both_index_key; 142drop index both_index_expr_key; 143-- 144-- Make sure that cross matching of attribute opclass/collation does not occur 145-- 146create unique index cross_match on insertconflicttest(lower(fruit) collate "C", upper(fruit) text_pattern_ops); 147-- fails: 148explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) text_pattern_ops, upper(fruit) collate "C") do nothing; 149ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 150-- works: 151explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) collate "C", upper(fruit) text_pattern_ops) do nothing; 152 QUERY PLAN 153----------------------------------------- 154 Insert on insertconflicttest 155 Conflict Resolution: NOTHING 156 Conflict Arbiter Indexes: cross_match 157 -> Result 158(4 rows) 159 160drop index cross_match; 161-- 162-- Single key tests 163-- 164create unique index key_index on insertconflicttest(key); 165-- 166-- Explain tests 167-- 168explain (costs off) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit; 169 QUERY PLAN 170--------------------------------------- 171 Insert on insertconflicttest 172 Conflict Resolution: UPDATE 173 Conflict Arbiter Indexes: key_index 174 -> Result 175(4 rows) 176 177-- Should display qual actually attributable to internal sequential scan: 178explain (costs off) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Cawesh'; 179 QUERY PLAN 180----------------------------------------------------------------- 181 Insert on insertconflicttest 182 Conflict Resolution: UPDATE 183 Conflict Arbiter Indexes: key_index 184 Conflict Filter: (insertconflicttest.fruit <> 'Cawesh'::text) 185 -> Result 186(5 rows) 187 188-- With EXCLUDED.* expression in scan node: 189explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key) do update set fruit = excluded.fruit where excluded.fruit != 'Elderberry'; 190 QUERY PLAN 191----------------------------------------------------------- 192 Insert on insertconflicttest 193 Conflict Resolution: UPDATE 194 Conflict Arbiter Indexes: key_index 195 Conflict Filter: (excluded.fruit <> 'Elderberry'::text) 196 -> Result 197(5 rows) 198 199-- Does the same, but JSON format shows "Conflict Arbiter Index" as JSON array: 200explain (costs off, format json) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Lime' returning *; 201 QUERY PLAN 202------------------------------------------------------------------------ 203 [ + 204 { + 205 "Plan": { + 206 "Node Type": "ModifyTable", + 207 "Operation": "Insert", + 208 "Parallel Aware": false, + 209 "Relation Name": "insertconflicttest", + 210 "Alias": "insertconflicttest", + 211 "Conflict Resolution": "UPDATE", + 212 "Conflict Arbiter Indexes": ["key_index"], + 213 "Conflict Filter": "(insertconflicttest.fruit <> 'Lime'::text)",+ 214 "Plans": [ + 215 { + 216 "Node Type": "Result", + 217 "Parent Relationship": "Member", + 218 "Parallel Aware": false + 219 } + 220 ] + 221 } + 222 } + 223 ] 224(1 row) 225 226-- Fails (no unique index inference specification, required for do update variant): 227insert into insertconflicttest values (1, 'Apple') on conflict do update set fruit = excluded.fruit; 228ERROR: ON CONFLICT DO UPDATE requires inference specification or constraint name 229LINE 1: ...nsert into insertconflicttest values (1, 'Apple') on conflic... 230 ^ 231HINT: For example, ON CONFLICT (column_name). 232-- inference succeeds: 233insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit; 234insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key) do update set fruit = excluded.fruit; 235-- Succeed, since multi-assignment does not involve subquery: 236insert into insertconflicttest 237values (1, 'Apple'), (2, 'Orange') 238on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key); 239-- Give good diagnostic message when EXCLUDED.* spuriously referenced from 240-- RETURNING: 241insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit; 242ERROR: invalid reference to FROM-clause entry for table "excluded" 243LINE 1: ...y) do update set fruit = excluded.fruit RETURNING excluded.f... 244 ^ 245HINT: There is an entry for table "excluded", but it cannot be referenced from this part of the query. 246-- Only suggest <table>.* column when inference element misspelled: 247insert into insertconflicttest values (1, 'Apple') on conflict (keyy) do update set fruit = excluded.fruit; 248ERROR: column "keyy" does not exist 249LINE 1: ...nsertconflicttest values (1, 'Apple') on conflict (keyy) do ... 250 ^ 251HINT: Perhaps you meant to reference the column "insertconflicttest.key". 252-- Have useful HINT for EXCLUDED.* RTE within UPDATE: 253insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruitt; 254ERROR: column excluded.fruitt does not exist 255LINE 1: ... 'Apple') on conflict (key) do update set fruit = excluded.f... 256 ^ 257HINT: Perhaps you meant to reference the column "excluded.fruit". 258-- inference fails: 259insert into insertconflicttest values (3, 'Kiwi') on conflict (key, fruit) do update set fruit = excluded.fruit; 260ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 261insert into insertconflicttest values (4, 'Mango') on conflict (fruit, key) do update set fruit = excluded.fruit; 262ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 263insert into insertconflicttest values (5, 'Lemon') on conflict (fruit) do update set fruit = excluded.fruit; 264ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 265insert into insertconflicttest values (6, 'Passionfruit') on conflict (lower(fruit)) do update set fruit = excluded.fruit; 266ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 267-- Check the target relation can be aliased 268insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = excluded.fruit; -- ok, no reference to target table 269insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = ict.fruit; -- ok, alias 270insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = insertconflicttest.fruit; -- error, references aliased away name 271ERROR: invalid reference to FROM-clause entry for table "insertconflicttest" 272LINE 1: ...onfruit') on conflict (key) do update set fruit = insertconf... 273 ^ 274HINT: Perhaps you meant to reference the table alias "ict". 275drop index key_index; 276-- 277-- Composite key tests 278-- 279create unique index comp_key_index on insertconflicttest(key, fruit); 280-- inference succeeds: 281insert into insertconflicttest values (7, 'Raspberry') on conflict (key, fruit) do update set fruit = excluded.fruit; 282insert into insertconflicttest values (8, 'Lime') on conflict (fruit, key) do update set fruit = excluded.fruit; 283-- inference fails: 284insert into insertconflicttest values (9, 'Banana') on conflict (key) do update set fruit = excluded.fruit; 285ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 286insert into insertconflicttest values (10, 'Blueberry') on conflict (key, key, key) do update set fruit = excluded.fruit; 287ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 288insert into insertconflicttest values (11, 'Cherry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit; 289ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 290insert into insertconflicttest values (12, 'Date') on conflict (lower(fruit), key) do update set fruit = excluded.fruit; 291ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 292drop index comp_key_index; 293-- 294-- Partial index tests, no inference predicate specified 295-- 296create unique index part_comp_key_index on insertconflicttest(key, fruit) where key < 5; 297create unique index expr_part_comp_key_index on insertconflicttest(key, lower(fruit)) where key < 5; 298-- inference fails: 299insert into insertconflicttest values (13, 'Grape') on conflict (key, fruit) do update set fruit = excluded.fruit; 300ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 301insert into insertconflicttest values (14, 'Raisin') on conflict (fruit, key) do update set fruit = excluded.fruit; 302ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 303insert into insertconflicttest values (15, 'Cranberry') on conflict (key) do update set fruit = excluded.fruit; 304ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 305insert into insertconflicttest values (16, 'Melon') on conflict (key, key, key) do update set fruit = excluded.fruit; 306ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 307insert into insertconflicttest values (17, 'Mulberry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit; 308ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 309insert into insertconflicttest values (18, 'Pineapple') on conflict (lower(fruit), key) do update set fruit = excluded.fruit; 310ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 311drop index part_comp_key_index; 312drop index expr_part_comp_key_index; 313-- 314-- Expression index tests 315-- 316create unique index expr_key_index on insertconflicttest(lower(fruit)); 317-- inference succeeds: 318insert into insertconflicttest values (20, 'Quince') on conflict (lower(fruit)) do update set fruit = excluded.fruit; 319insert into insertconflicttest values (21, 'Pomegranate') on conflict (lower(fruit), lower(fruit)) do update set fruit = excluded.fruit; 320-- inference fails: 321insert into insertconflicttest values (22, 'Apricot') on conflict (upper(fruit)) do update set fruit = excluded.fruit; 322ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 323insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) do update set fruit = excluded.fruit; 324ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 325drop index expr_key_index; 326-- 327-- Expression index tests (with regular column) 328-- 329create unique index expr_comp_key_index on insertconflicttest(key, lower(fruit)); 330create unique index tricky_expr_comp_key_index on insertconflicttest(key, lower(fruit), upper(fruit)); 331-- inference succeeds: 332insert into insertconflicttest values (24, 'Plum') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit; 333insert into insertconflicttest values (25, 'Peach') on conflict (lower(fruit), key) do update set fruit = excluded.fruit; 334-- Should not infer "tricky_expr_comp_key_index" index: 335explain (costs off) insert into insertconflicttest values (26, 'Fig') on conflict (lower(fruit), key, lower(fruit), key) do update set fruit = excluded.fruit; 336 QUERY PLAN 337------------------------------------------------- 338 Insert on insertconflicttest 339 Conflict Resolution: UPDATE 340 Conflict Arbiter Indexes: expr_comp_key_index 341 -> Result 342(4 rows) 343 344-- inference fails: 345insert into insertconflicttest values (27, 'Prune') on conflict (key, upper(fruit)) do update set fruit = excluded.fruit; 346ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 347insert into insertconflicttest values (28, 'Redcurrant') on conflict (fruit, key) do update set fruit = excluded.fruit; 348ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 349insert into insertconflicttest values (29, 'Nectarine') on conflict (key) do update set fruit = excluded.fruit; 350ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 351drop index expr_comp_key_index; 352drop index tricky_expr_comp_key_index; 353-- 354-- Non-spurious duplicate violation tests 355-- 356create unique index key_index on insertconflicttest(key); 357create unique index fruit_index on insertconflicttest(fruit); 358-- succeeds, since UPDATE happens to update "fruit" to existing value: 359insert into insertconflicttest values (26, 'Fig') on conflict (key) do update set fruit = excluded.fruit; 360-- fails, since UPDATE is to row with key value 26, and we're updating "fruit" 361-- to a value that happens to exist in another row ('peach'): 362insert into insertconflicttest values (26, 'Peach') on conflict (key) do update set fruit = excluded.fruit; 363ERROR: duplicate key value violates unique constraint "fruit_index" 364DETAIL: Key (fruit)=(Peach) already exists. 365-- succeeds, since "key" isn't repeated/referenced in UPDATE, and "fruit" 366-- arbitrates that statement updates existing "Fig" row: 367insert into insertconflicttest values (25, 'Fig') on conflict (fruit) do update set fruit = excluded.fruit; 368drop index key_index; 369drop index fruit_index; 370-- 371-- Test partial unique index inference 372-- 373create unique index partial_key_index on insertconflicttest(key) where fruit like '%berry'; 374-- Succeeds 375insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' do update set fruit = excluded.fruit; 376insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and fruit = 'inconsequential' do nothing; 377-- fails 378insert into insertconflicttest values (23, 'Blackberry') on conflict (key) do update set fruit = excluded.fruit; 379ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 380insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' or fruit = 'consequential' do nothing; 381ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 382insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) where fruit like '%berry' do update set fruit = excluded.fruit; 383ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification 384drop index partial_key_index; 385-- 386-- Test that wholerow references to ON CONFLICT's EXCLUDED work 387-- 388create unique index plain on insertconflicttest(key); 389-- Succeeds, updates existing row: 390insert into insertconflicttest as i values (23, 'Jackfruit') on conflict (key) do update set fruit = excluded.fruit 391 where i.* != excluded.* returning *; 392 key | fruit 393-----+----------- 394 23 | Jackfruit 395(1 row) 396 397-- No update this time, though: 398insert into insertconflicttest as i values (23, 'Jackfruit') on conflict (key) do update set fruit = excluded.fruit 399 where i.* != excluded.* returning *; 400 key | fruit 401-----+------- 402(0 rows) 403 404-- Predicate changed to require match rather than non-match, so updates once more: 405insert into insertconflicttest as i values (23, 'Jackfruit') on conflict (key) do update set fruit = excluded.fruit 406 where i.* = excluded.* returning *; 407 key | fruit 408-----+----------- 409 23 | Jackfruit 410(1 row) 411 412-- Assign: 413insert into insertconflicttest as i values (23, 'Avocado') on conflict (key) do update set fruit = excluded.*::text 414 returning *; 415 key | fruit 416-----+-------------- 417 23 | (23,Avocado) 418(1 row) 419 420-- deparse whole row var in WHERE and SET clauses: 421explain (costs off) insert into insertconflicttest as i values (23, 'Avocado') on conflict (key) do update set fruit = excluded.fruit where excluded.* is null; 422 QUERY PLAN 423----------------------------------------- 424 Insert on insertconflicttest i 425 Conflict Resolution: UPDATE 426 Conflict Arbiter Indexes: plain 427 Conflict Filter: (excluded.* IS NULL) 428 -> Result 429(5 rows) 430 431explain (costs off) insert into insertconflicttest as i values (23, 'Avocado') on conflict (key) do update set fruit = excluded.*::text; 432 QUERY PLAN 433----------------------------------- 434 Insert on insertconflicttest i 435 Conflict Resolution: UPDATE 436 Conflict Arbiter Indexes: plain 437 -> Result 438(4 rows) 439 440drop index plain; 441-- Cleanup 442drop table insertconflicttest; 443-- 444-- Verify that EXCLUDED does not allow system column references. These 445-- do not make sense because EXCLUDED isn't an already stored tuple 446-- (and thus doesn't have a ctid, oids are not assigned yet, etc). 447-- 448create table syscolconflicttest(key int4, data text) WITH OIDS; 449insert into syscolconflicttest values (1); 450insert into syscolconflicttest values (1) on conflict (key) do update set data = excluded.ctid::text; 451ERROR: column excluded.ctid does not exist 452LINE 1: ...values (1) on conflict (key) do update set data = excluded.c... 453 ^ 454insert into syscolconflicttest values (1) on conflict (key) do update set data = excluded.oid::text; 455ERROR: column excluded.oid does not exist 456LINE 1: ...values (1) on conflict (key) do update set data = excluded.o... 457 ^ 458drop table syscolconflicttest; 459-- 460-- Previous tests all managed to not test any expressions requiring 461-- planner preprocessing ... 462-- 463create table insertconflict (a bigint, b bigint); 464create unique index insertconflicti1 on insertconflict(coalesce(a, 0)); 465create unique index insertconflicti2 on insertconflict(b) 466 where coalesce(a, 1) > 0; 467insert into insertconflict values (1, 2) 468on conflict (coalesce(a, 0)) do nothing; 469insert into insertconflict values (1, 2) 470on conflict (b) where coalesce(a, 1) > 0 do nothing; 471insert into insertconflict values (1, 2) 472on conflict (b) where coalesce(a, 1) > 1 do nothing; 473drop table insertconflict; 474-- 475-- test insertion through view 476-- 477create table insertconflict (f1 int primary key, f2 text); 478create view insertconflictv as 479 select * from insertconflict with cascaded check option; 480insert into insertconflictv values (1,'foo') 481 on conflict (f1) do update set f2 = excluded.f2; 482select * from insertconflict; 483 f1 | f2 484----+----- 485 1 | foo 486(1 row) 487 488insert into insertconflictv values (1,'bar') 489 on conflict (f1) do update set f2 = excluded.f2; 490select * from insertconflict; 491 f1 | f2 492----+----- 493 1 | bar 494(1 row) 495 496drop view insertconflictv; 497drop table insertconflict; 498-- ****************************************************************** 499-- * * 500-- * Test inheritance (example taken from tutorial) * 501-- * * 502-- ****************************************************************** 503create table cities ( 504 name text, 505 population float8, 506 altitude int -- (in ft) 507); 508create table capitals ( 509 state char(2) 510) inherits (cities); 511-- Create unique indexes. Due to a general limitation of inheritance, 512-- uniqueness is only enforced per-relation. Unique index inference 513-- specification will do the right thing, though. 514create unique index cities_names_unique on cities (name); 515create unique index capitals_names_unique on capitals (name); 516-- prepopulate the tables. 517insert into cities values ('San Francisco', 7.24E+5, 63); 518insert into cities values ('Las Vegas', 2.583E+5, 2174); 519insert into cities values ('Mariposa', 1200, 1953); 520insert into capitals values ('Sacramento', 3.694E+5, 30, 'CA'); 521insert into capitals values ('Madison', 1.913E+5, 845, 'WI'); 522-- Tests proper for inheritance: 523select * from capitals; 524 name | population | altitude | state 525------------+------------+----------+------- 526 Sacramento | 369400 | 30 | CA 527 Madison | 191300 | 845 | WI 528(2 rows) 529 530-- Succeeds: 531insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict do nothing; 532insert into capitals values ('Sacramento', 4664.E+5, 30, 'CA') on conflict (name) do update set population = excluded.population; 533-- Wrong "Sacramento", so do nothing: 534insert into capitals values ('Sacramento', 50, 2267, 'NE') on conflict (name) do nothing; 535select * from capitals; 536 name | population | altitude | state 537------------+------------+----------+------- 538 Madison | 191300 | 845 | WI 539 Sacramento | 466400000 | 30 | CA 540(2 rows) 541 542insert into cities values ('Las Vegas', 5.83E+5, 2001) on conflict (name) do update set population = excluded.population, altitude = excluded.altitude; 543select tableoid::regclass, * from cities; 544 tableoid | name | population | altitude 545----------+---------------+------------+---------- 546 cities | San Francisco | 724000 | 63 547 cities | Mariposa | 1200 | 1953 548 cities | Las Vegas | 583000 | 2001 549 capitals | Madison | 191300 | 845 550 capitals | Sacramento | 466400000 | 30 551(5 rows) 552 553insert into capitals values ('Las Vegas', 5.83E+5, 2222, 'NV') on conflict (name) do update set population = excluded.population; 554-- Capitals will contain new capital, Las Vegas: 555select * from capitals; 556 name | population | altitude | state 557------------+------------+----------+------- 558 Madison | 191300 | 845 | WI 559 Sacramento | 466400000 | 30 | CA 560 Las Vegas | 583000 | 2222 | NV 561(3 rows) 562 563-- Cities contains two instances of "Las Vegas", since unique constraints don't 564-- work across inheritance: 565select tableoid::regclass, * from cities; 566 tableoid | name | population | altitude 567----------+---------------+------------+---------- 568 cities | San Francisco | 724000 | 63 569 cities | Mariposa | 1200 | 1953 570 cities | Las Vegas | 583000 | 2001 571 capitals | Madison | 191300 | 845 572 capitals | Sacramento | 466400000 | 30 573 capitals | Las Vegas | 583000 | 2222 574(6 rows) 575 576-- This only affects "cities" version of "Las Vegas": 577insert into cities values ('Las Vegas', 5.86E+5, 2223) on conflict (name) do update set population = excluded.population, altitude = excluded.altitude; 578select tableoid::regclass, * from cities; 579 tableoid | name | population | altitude 580----------+---------------+------------+---------- 581 cities | San Francisco | 724000 | 63 582 cities | Mariposa | 1200 | 1953 583 cities | Las Vegas | 586000 | 2223 584 capitals | Madison | 191300 | 845 585 capitals | Sacramento | 466400000 | 30 586 capitals | Las Vegas | 583000 | 2222 587(6 rows) 588 589-- clean up 590drop table capitals; 591drop table cities; 592-- Make sure a table named excluded is handled properly 593create table excluded(key int primary key, data text); 594insert into excluded values(1, '1'); 595-- error, ambiguous 596insert into excluded values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; 597ERROR: table reference "excluded" is ambiguous 598LINE 1: ...es(1, '2') on conflict (key) do update set data = excluded.d... 599 ^ 600-- ok, aliased 601insert into excluded AS target values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; 602 key | data 603-----+------ 604 1 | 2 605(1 row) 606 607-- ok, aliased 608insert into excluded AS target values(1, '2') on conflict (key) do update set data = target.data RETURNING *; 609 key | data 610-----+------ 611 1 | 2 612(1 row) 613 614-- make sure excluded isn't a problem in returning clause 615insert into excluded values(1, '2') on conflict (key) do update set data = 3 RETURNING excluded.*; 616 key | data 617-----+------ 618 1 | 3 619(1 row) 620 621-- clean up 622drop table excluded; 623-- Check tables w/o oids are handled correctly 624create table testoids(key int primary key, data text) without oids; 625-- first without oids 626insert into testoids values(1, '1') on conflict (key) do update set data = excluded.data RETURNING *; 627 key | data 628-----+------ 629 1 | 1 630(1 row) 631 632insert into testoids values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; 633 key | data 634-----+------ 635 1 | 2 636(1 row) 637 638-- add oids 639alter table testoids set with oids; 640-- update existing row, that didn't have an oid 641insert into testoids values(1, '3') on conflict (key) do update set data = excluded.data RETURNING *; 642 key | data 643-----+------ 644 1 | 3 645(1 row) 646 647-- insert a new row 648insert into testoids values(2, '1') on conflict (key) do update set data = excluded.data RETURNING *; 649 key | data 650-----+------ 651 2 | 1 652(1 row) 653 654-- and update it 655insert into testoids values(2, '2') on conflict (key) do update set data = excluded.data RETURNING *; 656 key | data 657-----+------ 658 2 | 2 659(1 row) 660 661-- remove oids again, test 662alter table testoids set without oids; 663insert into testoids values(1, '4') on conflict (key) do update set data = excluded.data RETURNING *; 664 key | data 665-----+------ 666 1 | 4 667(1 row) 668 669insert into testoids values(3, '1') on conflict (key) do update set data = excluded.data RETURNING *; 670 key | data 671-----+------ 672 3 | 1 673(1 row) 674 675insert into testoids values(3, '2') on conflict (key) do update set data = excluded.data RETURNING *; 676 key | data 677-----+------ 678 3 | 2 679(1 row) 680 681DROP TABLE testoids; 682-- check that references to columns after dropped columns are handled correctly 683create table dropcol(key int primary key, drop1 int, keep1 text, drop2 numeric, keep2 float); 684insert into dropcol(key, drop1, keep1, drop2, keep2) values(1, 1, '1', '1', 1); 685-- set using excluded 686insert into dropcol(key, drop1, keep1, drop2, keep2) values(1, 2, '2', '2', 2) on conflict(key) 687 do update set drop1 = excluded.drop1, keep1 = excluded.keep1, drop2 = excluded.drop2, keep2 = excluded.keep2 688 where excluded.drop1 is not null and excluded.keep1 is not null and excluded.drop2 is not null and excluded.keep2 is not null 689 and dropcol.drop1 is not null and dropcol.keep1 is not null and dropcol.drop2 is not null and dropcol.keep2 is not null 690 returning *; 691 key | drop1 | keep1 | drop2 | keep2 692-----+-------+-------+-------+------- 693 1 | 2 | 2 | 2 | 2 694(1 row) 695 696; 697-- set using existing table 698insert into dropcol(key, drop1, keep1, drop2, keep2) values(1, 3, '3', '3', 3) on conflict(key) 699 do update set drop1 = dropcol.drop1, keep1 = dropcol.keep1, drop2 = dropcol.drop2, keep2 = dropcol.keep2 700 returning *; 701 key | drop1 | keep1 | drop2 | keep2 702-----+-------+-------+-------+------- 703 1 | 2 | 2 | 2 | 2 704(1 row) 705 706; 707alter table dropcol drop column drop1, drop column drop2; 708-- set using excluded 709insert into dropcol(key, keep1, keep2) values(1, '4', 4) on conflict(key) 710 do update set keep1 = excluded.keep1, keep2 = excluded.keep2 711 where excluded.keep1 is not null and excluded.keep2 is not null 712 and dropcol.keep1 is not null and dropcol.keep2 is not null 713 returning *; 714 key | keep1 | keep2 715-----+-------+------- 716 1 | 4 | 4 717(1 row) 718 719; 720-- set using existing table 721insert into dropcol(key, keep1, keep2) values(1, '5', 5) on conflict(key) 722 do update set keep1 = dropcol.keep1, keep2 = dropcol.keep2 723 returning *; 724 key | keep1 | keep2 725-----+-------+------- 726 1 | 4 | 4 727(1 row) 728 729; 730DROP TABLE dropcol; 731-- check handling of regular btree constraint along with gist constraint 732create table twoconstraints (f1 int unique, f2 box, 733 exclude using gist(f2 with &&)); 734insert into twoconstraints values(1, '((0,0),(1,1))'); 735insert into twoconstraints values(1, '((2,2),(3,3))'); -- fail on f1 736ERROR: duplicate key value violates unique constraint "twoconstraints_f1_key" 737DETAIL: Key (f1)=(1) already exists. 738insert into twoconstraints values(2, '((0,0),(1,2))'); -- fail on f2 739ERROR: conflicting key value violates exclusion constraint "twoconstraints_f2_excl" 740DETAIL: Key (f2)=((1,2),(0,0)) conflicts with existing key (f2)=((1,1),(0,0)). 741insert into twoconstraints values(2, '((0,0),(1,2))') 742 on conflict on constraint twoconstraints_f1_key do nothing; -- fail on f2 743ERROR: conflicting key value violates exclusion constraint "twoconstraints_f2_excl" 744DETAIL: Key (f2)=((1,2),(0,0)) conflicts with existing key (f2)=((1,1),(0,0)). 745insert into twoconstraints values(2, '((0,0),(1,2))') 746 on conflict on constraint twoconstraints_f2_excl do nothing; -- do nothing 747select * from twoconstraints; 748 f1 | f2 749----+------------- 750 1 | (1,1),(0,0) 751(1 row) 752 753drop table twoconstraints; 754-- check handling of self-conflicts at various isolation levels 755create table selfconflict (f1 int primary key, f2 int); 756begin transaction isolation level read committed; 757insert into selfconflict values (1,1), (1,2) on conflict do nothing; 758commit; 759begin transaction isolation level repeatable read; 760insert into selfconflict values (2,1), (2,2) on conflict do nothing; 761commit; 762begin transaction isolation level serializable; 763insert into selfconflict values (3,1), (3,2) on conflict do nothing; 764commit; 765begin transaction isolation level read committed; 766insert into selfconflict values (4,1), (4,2) on conflict(f1) do update set f2 = 0; 767ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time 768HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. 769commit; 770begin transaction isolation level repeatable read; 771insert into selfconflict values (5,1), (5,2) on conflict(f1) do update set f2 = 0; 772ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time 773HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. 774commit; 775begin transaction isolation level serializable; 776insert into selfconflict values (6,1), (6,2) on conflict(f1) do update set f2 = 0; 777ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time 778HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. 779commit; 780select * from selfconflict; 781 f1 | f2 782----+---- 783 1 | 1 784 2 | 1 785 3 | 1 786(3 rows) 787 788drop table selfconflict; 789