src/test/regress/expected/insert_conflict.out

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