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 as t values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and t.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 etc). 257-- 258create table syscolconflicttest(key int4, data text); 259insert into syscolconflicttest values (1); 260insert into syscolconflicttest values (1) on conflict (key) do update set data = excluded.ctid::text; 261drop table syscolconflicttest; 262 263-- 264-- Previous tests all managed to not test any expressions requiring 265-- planner preprocessing ... 266-- 267create table insertconflict (a bigint, b bigint); 268 269create unique index insertconflicti1 on insertconflict(coalesce(a, 0)); 270 271create unique index insertconflicti2 on insertconflict(b) 272 where coalesce(a, 1) > 0; 273 274insert into insertconflict values (1, 2) 275on conflict (coalesce(a, 0)) do nothing; 276 277insert into insertconflict values (1, 2) 278on conflict (b) where coalesce(a, 1) > 0 do nothing; 279 280insert into insertconflict values (1, 2) 281on conflict (b) where coalesce(a, 1) > 1 do nothing; 282 283drop table insertconflict; 284 285-- 286-- test insertion through view 287-- 288 289create table insertconflict (f1 int primary key, f2 text); 290create view insertconflictv as 291 select * from insertconflict with cascaded check option; 292 293insert into insertconflictv values (1,'foo') 294 on conflict (f1) do update set f2 = excluded.f2; 295select * from insertconflict; 296insert into insertconflictv values (1,'bar') 297 on conflict (f1) do update set f2 = excluded.f2; 298select * from insertconflict; 299 300drop view insertconflictv; 301drop table insertconflict; 302 303 304-- ****************************************************************** 305-- * * 306-- * Test inheritance (example taken from tutorial) * 307-- * * 308-- ****************************************************************** 309create table cities ( 310 name text, 311 population float8, 312 altitude int -- (in ft) 313); 314 315create table capitals ( 316 state char(2) 317) inherits (cities); 318 319-- Create unique indexes. Due to a general limitation of inheritance, 320-- uniqueness is only enforced per-relation. Unique index inference 321-- specification will do the right thing, though. 322create unique index cities_names_unique on cities (name); 323create unique index capitals_names_unique on capitals (name); 324 325-- prepopulate the tables. 326insert into cities values ('San Francisco', 7.24E+5, 63); 327insert into cities values ('Las Vegas', 2.583E+5, 2174); 328insert into cities values ('Mariposa', 1200, 1953); 329 330insert into capitals values ('Sacramento', 3.694E+5, 30, 'CA'); 331insert into capitals values ('Madison', 1.913E+5, 845, 'WI'); 332 333-- Tests proper for inheritance: 334select * from capitals; 335 336-- Succeeds: 337insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict do nothing; 338insert into capitals values ('Sacramento', 4664.E+5, 30, 'CA') on conflict (name) do update set population = excluded.population; 339-- Wrong "Sacramento", so do nothing: 340insert into capitals values ('Sacramento', 50, 2267, 'NE') on conflict (name) do nothing; 341select * from capitals; 342insert into cities values ('Las Vegas', 5.83E+5, 2001) on conflict (name) do update set population = excluded.population, altitude = excluded.altitude; 343select tableoid::regclass, * from cities; 344insert into capitals values ('Las Vegas', 5.83E+5, 2222, 'NV') on conflict (name) do update set population = excluded.population; 345-- Capitals will contain new capital, Las Vegas: 346select * from capitals; 347-- Cities contains two instances of "Las Vegas", since unique constraints don't 348-- work across inheritance: 349select tableoid::regclass, * from cities; 350-- This only affects "cities" version of "Las Vegas": 351insert into cities values ('Las Vegas', 5.86E+5, 2223) on conflict (name) do update set population = excluded.population, altitude = excluded.altitude; 352select tableoid::regclass, * from cities; 353 354-- clean up 355drop table capitals; 356drop table cities; 357 358 359-- Make sure a table named excluded is handled properly 360create table excluded(key int primary key, data text); 361insert into excluded values(1, '1'); 362-- error, ambiguous 363insert into excluded values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; 364-- ok, aliased 365insert into excluded AS target values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; 366-- ok, aliased 367insert into excluded AS target values(1, '2') on conflict (key) do update set data = target.data RETURNING *; 368-- make sure excluded isn't a problem in returning clause 369insert into excluded values(1, '2') on conflict (key) do update set data = 3 RETURNING excluded.*; 370 371-- clean up 372drop table excluded; 373 374 375-- check that references to columns after dropped columns are handled correctly 376create table dropcol(key int primary key, drop1 int, keep1 text, drop2 numeric, keep2 float); 377insert into dropcol(key, drop1, keep1, drop2, keep2) values(1, 1, '1', '1', 1); 378-- set using excluded 379insert into dropcol(key, drop1, keep1, drop2, keep2) values(1, 2, '2', '2', 2) on conflict(key) 380 do update set drop1 = excluded.drop1, keep1 = excluded.keep1, drop2 = excluded.drop2, keep2 = excluded.keep2 381 where excluded.drop1 is not null and excluded.keep1 is not null and excluded.drop2 is not null and excluded.keep2 is not null 382 and dropcol.drop1 is not null and dropcol.keep1 is not null and dropcol.drop2 is not null and dropcol.keep2 is not null 383 returning *; 384; 385-- set using existing table 386insert into dropcol(key, drop1, keep1, drop2, keep2) values(1, 3, '3', '3', 3) on conflict(key) 387 do update set drop1 = dropcol.drop1, keep1 = dropcol.keep1, drop2 = dropcol.drop2, keep2 = dropcol.keep2 388 returning *; 389; 390alter table dropcol drop column drop1, drop column drop2; 391-- set using excluded 392insert into dropcol(key, keep1, keep2) values(1, '4', 4) on conflict(key) 393 do update set keep1 = excluded.keep1, keep2 = excluded.keep2 394 where excluded.keep1 is not null and excluded.keep2 is not null 395 and dropcol.keep1 is not null and dropcol.keep2 is not null 396 returning *; 397; 398-- set using existing table 399insert into dropcol(key, keep1, keep2) values(1, '5', 5) on conflict(key) 400 do update set keep1 = dropcol.keep1, keep2 = dropcol.keep2 401 returning *; 402; 403 404DROP TABLE dropcol; 405 406-- check handling of regular btree constraint along with gist constraint 407 408create table twoconstraints (f1 int unique, f2 box, 409 exclude using gist(f2 with &&)); 410insert into twoconstraints values(1, '((0,0),(1,1))'); 411insert into twoconstraints values(1, '((2,2),(3,3))'); -- fail on f1 412insert into twoconstraints values(2, '((0,0),(1,2))'); -- fail on f2 413insert into twoconstraints values(2, '((0,0),(1,2))') 414 on conflict on constraint twoconstraints_f1_key do nothing; -- fail on f2 415insert into twoconstraints values(2, '((0,0),(1,2))') 416 on conflict on constraint twoconstraints_f2_excl do nothing; -- do nothing 417select * from twoconstraints; 418drop table twoconstraints; 419 420-- check handling of self-conflicts at various isolation levels 421 422create table selfconflict (f1 int primary key, f2 int); 423 424begin transaction isolation level read committed; 425insert into selfconflict values (1,1), (1,2) on conflict do nothing; 426commit; 427 428begin transaction isolation level repeatable read; 429insert into selfconflict values (2,1), (2,2) on conflict do nothing; 430commit; 431 432begin transaction isolation level serializable; 433insert into selfconflict values (3,1), (3,2) on conflict do nothing; 434commit; 435 436begin transaction isolation level read committed; 437insert into selfconflict values (4,1), (4,2) on conflict(f1) do update set f2 = 0; 438commit; 439 440begin transaction isolation level repeatable read; 441insert into selfconflict values (5,1), (5,2) on conflict(f1) do update set f2 = 0; 442commit; 443 444begin transaction isolation level serializable; 445insert into selfconflict values (6,1), (6,2) on conflict(f1) do update set f2 = 0; 446commit; 447 448select * from selfconflict; 449 450drop table selfconflict; 451 452-- check ON CONFLICT handling with partitioned tables 453create table parted_conflict_test (a int unique, b char) partition by list (a); 454create table parted_conflict_test_1 partition of parted_conflict_test (b unique) for values in (1, 2); 455 456-- no indexes required here 457insert into parted_conflict_test values (1, 'a') on conflict do nothing; 458 459-- index on a required, which does exist in parent 460insert into parted_conflict_test values (1, 'a') on conflict (a) do nothing; 461insert into parted_conflict_test values (1, 'a') on conflict (a) do update set b = excluded.b; 462 463-- targeting partition directly will work 464insert into parted_conflict_test_1 values (1, 'a') on conflict (a) do nothing; 465insert into parted_conflict_test_1 values (1, 'b') on conflict (a) do update set b = excluded.b; 466 467-- index on b required, which doesn't exist in parent 468insert into parted_conflict_test values (2, 'b') on conflict (b) do update set a = excluded.a; 469 470-- targeting partition directly will work 471insert into parted_conflict_test_1 values (2, 'b') on conflict (b) do update set a = excluded.a; 472 473-- should see (2, 'b') 474select * from parted_conflict_test order by a; 475 476-- now check that DO UPDATE works correctly for target partition with 477-- different attribute numbers 478create table parted_conflict_test_2 (b char, a int unique); 479alter table parted_conflict_test attach partition parted_conflict_test_2 for values in (3); 480truncate parted_conflict_test; 481insert into parted_conflict_test values (3, 'a') on conflict (a) do update set b = excluded.b; 482insert into parted_conflict_test values (3, 'b') on conflict (a) do update set b = excluded.b; 483 484-- should see (3, 'b') 485select * from parted_conflict_test order by a; 486 487-- case where parent will have a dropped column, but the partition won't 488alter table parted_conflict_test drop b, add b char; 489create table parted_conflict_test_3 partition of parted_conflict_test for values in (4); 490truncate parted_conflict_test; 491insert into parted_conflict_test (a, b) values (4, 'a') on conflict (a) do update set b = excluded.b; 492insert into parted_conflict_test (a, b) values (4, 'b') on conflict (a) do update set b = excluded.b where parted_conflict_test.b = 'a'; 493 494-- should see (4, 'b') 495select * from parted_conflict_test order by a; 496 497-- case with multi-level partitioning 498create table parted_conflict_test_4 partition of parted_conflict_test for values in (5) partition by list (a); 499create table parted_conflict_test_4_1 partition of parted_conflict_test_4 for values in (5); 500truncate parted_conflict_test; 501insert into parted_conflict_test (a, b) values (5, 'a') on conflict (a) do update set b = excluded.b; 502insert into parted_conflict_test (a, b) values (5, 'b') on conflict (a) do update set b = excluded.b where parted_conflict_test.b = 'a'; 503 504-- should see (5, 'b') 505select * from parted_conflict_test order by a; 506 507-- test with multiple rows 508truncate parted_conflict_test; 509insert 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'; 510insert 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'; 511 512-- should see (1, 'b'), (2, 'a'), (4, 'b') 513select * from parted_conflict_test order by a; 514 515drop table parted_conflict_test; 516 517-- test behavior of inserting a conflicting tuple into an intermediate 518-- partitioning level 519create table parted_conflict (a int primary key, b text) partition by range (a); 520create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000) partition by range (a); 521create table parted_conflict_1_1 partition of parted_conflict_1 for values from (0) to (500); 522insert into parted_conflict values (40, 'forty'); 523insert into parted_conflict_1 values (40, 'cuarenta') 524 on conflict (a) do update set b = excluded.b; 525drop table parted_conflict; 526 527-- same thing, but this time try to use an index that's created not in the 528-- partition 529create table parted_conflict (a int, b text) partition by range (a); 530create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000) partition by range (a); 531create table parted_conflict_1_1 partition of parted_conflict_1 for values from (0) to (500); 532create unique index on only parted_conflict_1 (a); 533create unique index on only parted_conflict (a); 534alter index parted_conflict_a_idx attach partition parted_conflict_1_a_idx; 535insert into parted_conflict values (40, 'forty'); 536insert into parted_conflict_1 values (40, 'cuarenta') 537 on conflict (a) do update set b = excluded.b; 538drop table parted_conflict; 539 540-- test whole-row Vars in ON CONFLICT expressions 541create table parted_conflict (a int, b text, c int) partition by range (a); 542create table parted_conflict_1 (drp text, c int, a int, b text); 543alter table parted_conflict_1 drop column drp; 544create unique index on parted_conflict (a, b); 545alter table parted_conflict attach partition parted_conflict_1 for values from (0) to (1000); 546truncate parted_conflict; 547insert into parted_conflict values (50, 'cincuenta', 1); 548insert into parted_conflict values (50, 'cincuenta', 2) 549 on conflict (a, b) do update set (a, b, c) = row(excluded.*) 550 where parted_conflict = (50, text 'cincuenta', 1) and 551 excluded = (50, text 'cincuenta', 2); 552 553-- should see (50, 'cincuenta', 2) 554select * from parted_conflict order by a; 555 556-- test with statement level triggers 557create or replace function parted_conflict_update_func() returns trigger as $$ 558declare 559 r record; 560begin 561 for r in select * from inserted loop 562 raise notice 'a = %, b = %, c = %', r.a, r.b, r.c; 563 end loop; 564 return new; 565end; 566$$ language plpgsql; 567 568create trigger parted_conflict_update 569 after update on parted_conflict 570 referencing new table as inserted 571 for each statement 572 execute procedure parted_conflict_update_func(); 573 574truncate parted_conflict; 575 576insert into parted_conflict values (0, 'cero', 1); 577 578insert into parted_conflict values(0, 'cero', 1) 579 on conflict (a,b) do update set c = parted_conflict.c + 1; 580 581drop table parted_conflict; 582drop function parted_conflict_update_func(); 583