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