1-- 2-- insert...on conflict do unique index inference 3-- 4create table insertconflicttest(key int4, fruit text); 5 6-- 7-- Test unique index inference with operator class specifications and 8-- named collations 9-- 10create unique index op_index_key on insertconflicttest(key, fruit text_pattern_ops); 11create unique index collation_index_key on insertconflicttest(key, fruit collate "C"); 12create unique index both_index_key on insertconflicttest(key, fruit collate "C" text_pattern_ops); 13create unique index both_index_expr_key on insertconflicttest(key, lower(fruit) collate "C" text_pattern_ops); 14 15-- fails 16explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key) do nothing; 17explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit) do nothing; 18 19-- succeeds 20explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do nothing; 21explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit, key, fruit, key) do nothing; 22explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit), key, lower(fruit), key) do nothing; 23explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do update set fruit = excluded.fruit 24 where exists (select 1 from insertconflicttest ii where ii.key = excluded.key); 25-- Neither collation nor operator class specifications are required -- 26-- supplying them merely *limits* matches to indexes with matching opclasses 27-- used for relevant indexes 28explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit text_pattern_ops) do nothing; 29-- Okay, arbitrates using both index where text_pattern_ops opclass does and 30-- does not appear. 31explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit collate "C") do nothing; 32-- Okay, but only accepts the single index where both opclass and collation are 33-- specified 34explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit collate "C" text_pattern_ops, key) do nothing; 35-- Okay, but only accepts the single index where both opclass and collation are 36-- specified (plus expression variant) 37explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) collate "C", key, key) do nothing; 38-- Attribute appears twice, while not all attributes/expressions on attributes 39-- appearing within index definition match in terms of both opclass and 40-- collation. 41-- 42-- Works because every attribute in inference specification needs to be 43-- satisfied once or more by cataloged index attribute, and as always when an 44-- attribute in the cataloged definition has a non-default opclass/collation, 45-- it still satisfied some inference attribute lacking any particular 46-- opclass/collation specification. 47-- 48-- The implementation is liberal in accepting inference specifications on the 49-- assumption that multiple inferred unique indexes will prevent problematic 50-- cases. It rolls with unique indexes where attributes redundantly appear 51-- multiple times, too (which is not tested here). 52explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit, key, fruit text_pattern_ops, key) do nothing; 53explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) collate "C" text_pattern_ops, key, key) do nothing; 54 55drop index op_index_key; 56drop index collation_index_key; 57drop index both_index_key; 58drop index both_index_expr_key; 59 60-- 61-- Make sure that cross matching of attribute opclass/collation does not occur 62-- 63create unique index cross_match on insertconflicttest(lower(fruit) collate "C", upper(fruit) text_pattern_ops); 64 65-- fails: 66explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) text_pattern_ops, upper(fruit) collate "C") do nothing; 67-- works: 68explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) collate "C", upper(fruit) text_pattern_ops) do nothing; 69 70drop index cross_match; 71 72-- 73-- Single key tests 74-- 75create unique index key_index on insertconflicttest(key); 76 77-- 78-- Explain tests 79-- 80explain (costs off) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit; 81-- Should display qual actually attributable to internal sequential scan: 82explain (costs off) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Cawesh'; 83-- With EXCLUDED.* expression in scan node: 84explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key) do update set fruit = excluded.fruit where excluded.fruit != 'Elderberry'; 85-- Does the same, but JSON format shows "Conflict Arbiter Index" as JSON array: 86explain (costs off, format json) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Lime' returning *; 87 88-- Fails (no unique index inference specification, required for do update variant): 89insert into insertconflicttest values (1, 'Apple') on conflict do update set fruit = excluded.fruit; 90 91-- inference succeeds: 92insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit; 93insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key) do update set fruit = excluded.fruit; 94 95-- Succeed, since multi-assignment does not involve subquery: 96insert into insertconflicttest 97values (1, 'Apple'), (2, 'Orange') 98on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key); 99 100-- Give good diagnostic message when EXCLUDED.* spuriously referenced from 101-- RETURNING: 102insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit; 103 104-- Only suggest <table>.* column when inference element misspelled: 105insert into insertconflicttest values (1, 'Apple') on conflict (keyy) do update set fruit = excluded.fruit; 106 107-- Have useful HINT for EXCLUDED.* RTE within UPDATE: 108insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruitt; 109 110-- inference fails: 111insert into insertconflicttest values (3, 'Kiwi') on conflict (key, fruit) do update set fruit = excluded.fruit; 112insert into insertconflicttest values (4, 'Mango') on conflict (fruit, key) do update set fruit = excluded.fruit; 113insert into insertconflicttest values (5, 'Lemon') on conflict (fruit) do update set fruit = excluded.fruit; 114insert into insertconflicttest values (6, 'Passionfruit') on conflict (lower(fruit)) do update set fruit = excluded.fruit; 115 116-- Check the target relation can be aliased 117insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = excluded.fruit; -- ok, no reference to target table 118insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = ict.fruit; -- ok, alias 119insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = insertconflicttest.fruit; -- error, references aliased away name 120 121drop index key_index; 122 123-- 124-- Composite key tests 125-- 126create unique index comp_key_index on insertconflicttest(key, fruit); 127 128-- inference succeeds: 129insert into insertconflicttest values (7, 'Raspberry') on conflict (key, fruit) do update set fruit = excluded.fruit; 130insert into insertconflicttest values (8, 'Lime') on conflict (fruit, key) do update set fruit = excluded.fruit; 131 132-- inference fails: 133insert into insertconflicttest values (9, 'Banana') on conflict (key) do update set fruit = excluded.fruit; 134insert into insertconflicttest values (10, 'Blueberry') on conflict (key, key, key) do update set fruit = excluded.fruit; 135insert into insertconflicttest values (11, 'Cherry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit; 136insert into insertconflicttest values (12, 'Date') on conflict (lower(fruit), key) do update set fruit = excluded.fruit; 137 138drop index comp_key_index; 139 140-- 141-- Partial index tests, no inference predicate specified 142-- 143create unique index part_comp_key_index on insertconflicttest(key, fruit) where key < 5; 144create unique index expr_part_comp_key_index on insertconflicttest(key, lower(fruit)) where key < 5; 145 146-- inference fails: 147insert into insertconflicttest values (13, 'Grape') on conflict (key, fruit) do update set fruit = excluded.fruit; 148insert into insertconflicttest values (14, 'Raisin') on conflict (fruit, key) do update set fruit = excluded.fruit; 149insert into insertconflicttest values (15, 'Cranberry') on conflict (key) do update set fruit = excluded.fruit; 150insert into insertconflicttest values (16, 'Melon') on conflict (key, key, key) do update set fruit = excluded.fruit; 151insert into insertconflicttest values (17, 'Mulberry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit; 152insert into insertconflicttest values (18, 'Pineapple') on conflict (lower(fruit), key) do update set fruit = excluded.fruit; 153 154drop index part_comp_key_index; 155drop index expr_part_comp_key_index; 156 157-- 158-- Expression index tests 159-- 160create unique index expr_key_index on insertconflicttest(lower(fruit)); 161 162-- inference succeeds: 163insert into insertconflicttest values (20, 'Quince') on conflict (lower(fruit)) do update set fruit = excluded.fruit; 164insert into insertconflicttest values (21, 'Pomegranate') on conflict (lower(fruit), lower(fruit)) do update set fruit = excluded.fruit; 165 166-- inference fails: 167insert into insertconflicttest values (22, 'Apricot') on conflict (upper(fruit)) do update set fruit = excluded.fruit; 168insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) do update set fruit = excluded.fruit; 169 170drop index expr_key_index; 171 172-- 173-- Expression index tests (with regular column) 174-- 175create unique index expr_comp_key_index on insertconflicttest(key, lower(fruit)); 176create unique index tricky_expr_comp_key_index on insertconflicttest(key, lower(fruit), upper(fruit)); 177 178-- inference succeeds: 179insert into insertconflicttest values (24, 'Plum') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit; 180insert into insertconflicttest values (25, 'Peach') on conflict (lower(fruit), key) do update set fruit = excluded.fruit; 181-- Should not infer "tricky_expr_comp_key_index" index: 182explain (costs off) insert into insertconflicttest values (26, 'Fig') on conflict (lower(fruit), key, lower(fruit), key) do update set fruit = excluded.fruit; 183 184-- inference fails: 185insert into insertconflicttest values (27, 'Prune') on conflict (key, upper(fruit)) do update set fruit = excluded.fruit; 186insert into insertconflicttest values (28, 'Redcurrant') on conflict (fruit, key) do update set fruit = excluded.fruit; 187insert into insertconflicttest values (29, 'Nectarine') on conflict (key) do update set fruit = excluded.fruit; 188 189drop index expr_comp_key_index; 190drop index tricky_expr_comp_key_index; 191 192-- 193-- Non-spurious duplicate violation tests 194-- 195create unique index key_index on insertconflicttest(key); 196create unique index fruit_index on insertconflicttest(fruit); 197 198-- succeeds, since UPDATE happens to update "fruit" to existing value: 199insert into insertconflicttest values (26, 'Fig') on conflict (key) do update set fruit = excluded.fruit; 200-- fails, since UPDATE is to row with key value 26, and we're updating "fruit" 201-- to a value that happens to exist in another row ('peach'): 202insert into insertconflicttest values (26, 'Peach') on conflict (key) do update set fruit = excluded.fruit; 203-- succeeds, since "key" isn't repeated/referenced in UPDATE, and "fruit" 204-- arbitrates that statement updates existing "Fig" row: 205insert into insertconflicttest values (25, 'Fig') on conflict (fruit) do update set fruit = excluded.fruit; 206 207drop index key_index; 208drop index fruit_index; 209 210-- 211-- Test partial unique index inference 212-- 213create unique index partial_key_index on insertconflicttest(key) where fruit like '%berry'; 214 215-- Succeeds 216insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' do update set fruit = excluded.fruit; 217insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and fruit = 'inconsequential' do nothing; 218 219-- fails 220insert into insertconflicttest values (23, 'Blackberry') on conflict (key) do update set fruit = excluded.fruit; 221insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' or fruit = 'consequential' do nothing; 222insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) where fruit like '%berry' do update set fruit = excluded.fruit; 223 224drop index partial_key_index; 225 226-- 227-- Test that wholerow references to ON CONFLICT's EXCLUDED work 228-- 229create unique index plain on insertconflicttest(key); 230 231-- Succeeds, updates existing row: 232insert into insertconflicttest as i values (23, 'Jackfruit') on conflict (key) do update set fruit = excluded.fruit 233 where i.* != excluded.* returning *; 234-- No update this time, though: 235insert into insertconflicttest as i values (23, 'Jackfruit') on conflict (key) do update set fruit = excluded.fruit 236 where i.* != excluded.* returning *; 237-- Predicate changed to require match rather than non-match, so updates once more: 238insert into insertconflicttest as i values (23, 'Jackfruit') on conflict (key) do update set fruit = excluded.fruit 239 where i.* = excluded.* returning *; 240-- Assign: 241insert into insertconflicttest as i values (23, 'Avocado') on conflict (key) do update set fruit = excluded.*::text 242 returning *; 243-- deparse whole row var in WHERE and SET clauses: 244explain (costs off) insert into insertconflicttest as i values (23, 'Avocado') on conflict (key) do update set fruit = excluded.fruit where excluded.* is null; 245explain (costs off) insert into insertconflicttest as i values (23, 'Avocado') on conflict (key) do update set fruit = excluded.*::text; 246 247drop index plain; 248 249-- Cleanup 250drop table insertconflicttest; 251 252 253-- 254-- Verify that EXCLUDED does not allow system column references. These 255-- do not make sense because EXCLUDED isn't an already stored tuple 256-- (and thus doesn't have a ctid, oids are not assigned yet, etc). 257-- 258create table syscolconflicttest(key int4, data text) WITH OIDS; 259insert into syscolconflicttest values (1); 260insert into syscolconflicttest values (1) on conflict (key) do update set data = excluded.ctid::text; 261insert into syscolconflicttest values (1) on conflict (key) do update set data = excluded.oid::text; 262drop table syscolconflicttest; 263 264-- 265-- Previous tests all managed to not test any expressions requiring 266-- planner preprocessing ... 267-- 268create table insertconflict (a bigint, b bigint); 269 270create unique index insertconflicti1 on insertconflict(coalesce(a, 0)); 271 272create unique index insertconflicti2 on insertconflict(b) 273 where coalesce(a, 1) > 0; 274 275insert into insertconflict values (1, 2) 276on conflict (coalesce(a, 0)) do nothing; 277 278insert into insertconflict values (1, 2) 279on conflict (b) where coalesce(a, 1) > 0 do nothing; 280 281insert into insertconflict values (1, 2) 282on conflict (b) where coalesce(a, 1) > 1 do nothing; 283 284drop table insertconflict; 285 286-- 287-- test insertion through view 288-- 289 290create table insertconflict (f1 int primary key, f2 text); 291create view insertconflictv as 292 select * from insertconflict with cascaded check option; 293 294insert into insertconflictv values (1,'foo') 295 on conflict (f1) do update set f2 = excluded.f2; 296select * from insertconflict; 297insert into insertconflictv values (1,'bar') 298 on conflict (f1) do update set f2 = excluded.f2; 299select * from insertconflict; 300 301drop view insertconflictv; 302drop table insertconflict; 303 304 305-- ****************************************************************** 306-- * * 307-- * Test inheritance (example taken from tutorial) * 308-- * * 309-- ****************************************************************** 310create table cities ( 311 name text, 312 population float8, 313 altitude int -- (in ft) 314); 315 316create table capitals ( 317 state char(2) 318) inherits (cities); 319 320-- Create unique indexes. Due to a general limitation of inheritance, 321-- uniqueness is only enforced per-relation. Unique index inference 322-- specification will do the right thing, though. 323create unique index cities_names_unique on cities (name); 324create unique index capitals_names_unique on capitals (name); 325 326-- prepopulate the tables. 327insert into cities values ('San Francisco', 7.24E+5, 63); 328insert into cities values ('Las Vegas', 2.583E+5, 2174); 329insert into cities values ('Mariposa', 1200, 1953); 330 331insert into capitals values ('Sacramento', 3.694E+5, 30, 'CA'); 332insert into capitals values ('Madison', 1.913E+5, 845, 'WI'); 333 334-- Tests proper for inheritance: 335select * from capitals; 336 337-- Succeeds: 338insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict do nothing; 339insert into capitals values ('Sacramento', 4664.E+5, 30, 'CA') on conflict (name) do update set population = excluded.population; 340-- Wrong "Sacramento", so do nothing: 341insert into capitals values ('Sacramento', 50, 2267, 'NE') on conflict (name) do nothing; 342select * from capitals; 343insert into cities values ('Las Vegas', 5.83E+5, 2001) on conflict (name) do update set population = excluded.population, altitude = excluded.altitude; 344select tableoid::regclass, * from cities; 345insert into capitals values ('Las Vegas', 5.83E+5, 2222, 'NV') on conflict (name) do update set population = excluded.population; 346-- Capitals will contain new capital, Las Vegas: 347select * from capitals; 348-- Cities contains two instances of "Las Vegas", since unique constraints don't 349-- work across inheritance: 350select tableoid::regclass, * from cities; 351-- This only affects "cities" version of "Las Vegas": 352insert into cities values ('Las Vegas', 5.86E+5, 2223) on conflict (name) do update set population = excluded.population, altitude = excluded.altitude; 353select tableoid::regclass, * from cities; 354 355-- clean up 356drop table capitals; 357drop table cities; 358 359 360-- Make sure a table named excluded is handled properly 361create table excluded(key int primary key, data text); 362insert into excluded values(1, '1'); 363-- error, ambiguous 364insert into excluded values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; 365-- ok, aliased 366insert into excluded AS target values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; 367-- ok, aliased 368insert into excluded AS target values(1, '2') on conflict (key) do update set data = target.data RETURNING *; 369-- make sure excluded isn't a problem in returning clause 370insert into excluded values(1, '2') on conflict (key) do update set data = 3 RETURNING excluded.*; 371 372-- clean up 373drop table excluded; 374 375 376-- Check tables w/o oids are handled correctly 377create table testoids(key int primary key, data text) without oids; 378-- first without oids 379insert into testoids values(1, '1') on conflict (key) do update set data = excluded.data RETURNING *; 380insert into testoids values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; 381-- add oids 382alter table testoids set with oids; 383-- update existing row, that didn't have an oid 384insert into testoids values(1, '3') on conflict (key) do update set data = excluded.data RETURNING *; 385-- insert a new row 386insert into testoids values(2, '1') on conflict (key) do update set data = excluded.data RETURNING *; 387-- and update it 388insert into testoids values(2, '2') on conflict (key) do update set data = excluded.data RETURNING *; 389-- remove oids again, test 390alter table testoids set without oids; 391insert into testoids values(1, '4') on conflict (key) do update set data = excluded.data RETURNING *; 392insert into testoids values(3, '1') on conflict (key) do update set data = excluded.data RETURNING *; 393insert into testoids values(3, '2') on conflict (key) do update set data = excluded.data RETURNING *; 394 395DROP TABLE testoids; 396 397 398-- check that references to columns after dropped columns are handled correctly 399create table dropcol(key int primary key, drop1 int, keep1 text, drop2 numeric, keep2 float); 400insert into dropcol(key, drop1, keep1, drop2, keep2) values(1, 1, '1', '1', 1); 401-- set using excluded 402insert into dropcol(key, drop1, keep1, drop2, keep2) values(1, 2, '2', '2', 2) on conflict(key) 403 do update set drop1 = excluded.drop1, keep1 = excluded.keep1, drop2 = excluded.drop2, keep2 = excluded.keep2 404 where excluded.drop1 is not null and excluded.keep1 is not null and excluded.drop2 is not null and excluded.keep2 is not null 405 and dropcol.drop1 is not null and dropcol.keep1 is not null and dropcol.drop2 is not null and dropcol.keep2 is not null 406 returning *; 407; 408-- set using existing table 409insert into dropcol(key, drop1, keep1, drop2, keep2) values(1, 3, '3', '3', 3) on conflict(key) 410 do update set drop1 = dropcol.drop1, keep1 = dropcol.keep1, drop2 = dropcol.drop2, keep2 = dropcol.keep2 411 returning *; 412; 413alter table dropcol drop column drop1, drop column drop2; 414-- set using excluded 415insert into dropcol(key, keep1, keep2) values(1, '4', 4) on conflict(key) 416 do update set keep1 = excluded.keep1, keep2 = excluded.keep2 417 where excluded.keep1 is not null and excluded.keep2 is not null 418 and dropcol.keep1 is not null and dropcol.keep2 is not null 419 returning *; 420; 421-- set using existing table 422insert into dropcol(key, keep1, keep2) values(1, '5', 5) on conflict(key) 423 do update set keep1 = dropcol.keep1, keep2 = dropcol.keep2 424 returning *; 425; 426 427DROP TABLE dropcol; 428 429-- check handling of regular btree constraint along with gist constraint 430 431create table twoconstraints (f1 int unique, f2 box, 432 exclude using gist(f2 with &&)); 433insert into twoconstraints values(1, '((0,0),(1,1))'); 434insert into twoconstraints values(1, '((2,2),(3,3))'); -- fail on f1 435insert into twoconstraints values(2, '((0,0),(1,2))'); -- fail on f2 436insert into twoconstraints values(2, '((0,0),(1,2))') 437 on conflict on constraint twoconstraints_f1_key do nothing; -- fail on f2 438insert into twoconstraints values(2, '((0,0),(1,2))') 439 on conflict on constraint twoconstraints_f2_excl do nothing; -- do nothing 440select * from twoconstraints; 441drop table twoconstraints; 442 443-- check handling of self-conflicts at various isolation levels 444 445create table selfconflict (f1 int primary key, f2 int); 446 447begin transaction isolation level read committed; 448insert into selfconflict values (1,1), (1,2) on conflict do nothing; 449commit; 450 451begin transaction isolation level repeatable read; 452insert into selfconflict values (2,1), (2,2) on conflict do nothing; 453commit; 454 455begin transaction isolation level serializable; 456insert into selfconflict values (3,1), (3,2) on conflict do nothing; 457commit; 458 459begin transaction isolation level read committed; 460insert into selfconflict values (4,1), (4,2) on conflict(f1) do update set f2 = 0; 461commit; 462 463begin transaction isolation level repeatable read; 464insert into selfconflict values (5,1), (5,2) on conflict(f1) do update set f2 = 0; 465commit; 466 467begin transaction isolation level serializable; 468insert into selfconflict values (6,1), (6,2) on conflict(f1) do update set f2 = 0; 469commit; 470 471select * from selfconflict; 472 473drop table selfconflict; 474 475-- check ON CONFLICT handling with partitioned tables 476create table parted_conflict_test (a int unique, b char) partition by list (a); 477create table parted_conflict_test_1 partition of parted_conflict_test (b unique) for values in (1, 2); 478 479-- no indexes required here 480insert into parted_conflict_test values (1, 'a') on conflict do nothing; 481 482-- index on a required, which does exist in parent 483insert into parted_conflict_test values (1, 'a') on conflict (a) do nothing; 484insert into parted_conflict_test values (1, 'a') on conflict (a) do update set b = excluded.b; 485 486-- targeting partition directly will work 487insert into parted_conflict_test_1 values (1, 'a') on conflict (a) do nothing; 488insert into parted_conflict_test_1 values (1, 'b') on conflict (a) do update set b = excluded.b; 489 490-- index on b required, which doesn't exist in parent 491insert into parted_conflict_test values (2, 'b') on conflict (b) do update set a = excluded.a; 492 493-- targeting partition directly will work 494insert into parted_conflict_test_1 values (2, 'b') on conflict (b) do update set a = excluded.a; 495 496-- should see (2, 'b') 497select * from parted_conflict_test order by a; 498 499-- now check that DO UPDATE works correctly for target partition with 500-- different attribute numbers 501create table parted_conflict_test_2 (b char, a int unique); 502alter table parted_conflict_test attach partition parted_conflict_test_2 for values in (3); 503truncate parted_conflict_test; 504insert into parted_conflict_test values (3, 'a') on conflict (a) do update set b = excluded.b; 505insert into parted_conflict_test values (3, 'b') on conflict (a) do update set b = excluded.b; 506 507-- should see (3, 'b') 508select * from parted_conflict_test order by a; 509 510-- case where parent will have a dropped column, but the partition won't 511alter table parted_conflict_test drop b, add b char; 512create table parted_conflict_test_3 partition of parted_conflict_test for values in (4); 513truncate parted_conflict_test; 514insert into parted_conflict_test (a, b) values (4, 'a') on conflict (a) do update set b = excluded.b; 515insert into parted_conflict_test (a, b) values (4, 'b') on conflict (a) do update set b = excluded.b where parted_conflict_test.b = 'a'; 516 517-- should see (4, 'b') 518select * from parted_conflict_test order by a; 519 520-- case with multi-level partitioning 521create table parted_conflict_test_4 partition of parted_conflict_test for values in (5) partition by list (a); 522create table parted_conflict_test_4_1 partition of parted_conflict_test_4 for values in (5); 523truncate parted_conflict_test; 524insert into parted_conflict_test (a, b) values (5, 'a') on conflict (a) do update set b = excluded.b; 525insert into parted_conflict_test (a, b) values (5, 'b') on conflict (a) do update set b = excluded.b where parted_conflict_test.b = 'a'; 526 527-- should see (5, 'b') 528select * from parted_conflict_test order by a; 529 530-- test with multiple rows 531truncate parted_conflict_test; 532insert 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'; 533insert 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'; 534 535-- should see (1, 'b'), (2, 'a'), (4, 'b') 536select * from parted_conflict_test order by a; 537 538drop table parted_conflict_test; 539 540-- test behavior of inserting a conflicting tuple into an intermediate 541-- partitioning level 542create table parted_conflict (a int primary key, b text) partition by range (a); 543create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000) partition by range (a); 544create table parted_conflict_1_1 partition of parted_conflict_1 for values from (0) to (500); 545insert into parted_conflict values (40, 'forty'); 546insert into parted_conflict_1 values (40, 'cuarenta') 547 on conflict (a) do update set b = excluded.b; 548drop table parted_conflict; 549 550-- same thing, but this time try to use an index that's created not in the 551-- partition 552create table parted_conflict (a int, b text) partition by range (a); 553create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000) partition by range (a); 554create table parted_conflict_1_1 partition of parted_conflict_1 for values from (0) to (500); 555create unique index on only parted_conflict_1 (a); 556create unique index on only parted_conflict (a); 557alter index parted_conflict_a_idx attach partition parted_conflict_1_a_idx; 558insert into parted_conflict values (40, 'forty'); 559insert into parted_conflict_1 values (40, 'cuarenta') 560 on conflict (a) do update set b = excluded.b; 561drop table parted_conflict; 562 563-- test whole-row Vars in ON CONFLICT expressions 564create table parted_conflict (a int, b text, c int) partition by range (a); 565create table parted_conflict_1 (drp text, c int, a int, b text); 566alter table parted_conflict_1 drop column drp; 567create unique index on parted_conflict (a, b); 568alter table parted_conflict attach partition parted_conflict_1 for values from (0) to (1000); 569truncate parted_conflict; 570insert into parted_conflict values (50, 'cincuenta', 1); 571insert into parted_conflict values (50, 'cincuenta', 2) 572 on conflict (a, b) do update set (a, b, c) = row(excluded.*) 573 where parted_conflict = (50, text 'cincuenta', 1) and 574 excluded = (50, text 'cincuenta', 2); 575 576-- should see (50, 'cincuenta', 2) 577select * from parted_conflict order by a; 578 579drop table parted_conflict; 580