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