1-- 2-- insert with DEFAULT in the target_list 3-- 4create table inserttest (col1 int4, col2 int4 NOT NULL, col3 text default 'testing'); 5insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT, DEFAULT); 6insert into inserttest (col2, col3) values (3, DEFAULT); 7insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT); 8insert into inserttest values (DEFAULT, 5, 'test'); 9insert into inserttest values (DEFAULT, 7); 10 11select * from inserttest; 12 13-- 14-- insert with similar expression / target_list values (all fail) 15-- 16insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT); 17insert into inserttest (col1, col2, col3) values (1, 2); 18insert into inserttest (col1) values (1, 2); 19insert into inserttest (col1) values (DEFAULT, DEFAULT); 20 21select * from inserttest; 22 23-- 24-- VALUES test 25-- 26insert into inserttest values(10, 20, '40'), (-1, 2, DEFAULT), 27 ((select 2), (select i from (values(3)) as foo (i)), 'values are fun!'); 28 29select * from inserttest; 30 31-- 32-- TOASTed value test 33-- 34insert into inserttest values(30, 50, repeat('x', 10000)); 35 36select col1, col2, char_length(col3) from inserttest; 37 38drop table inserttest; 39 40-- 41-- check indirection (field/array assignment), cf bug #14265 42-- 43-- these tests are aware that transformInsertStmt has 3 separate code paths 44-- 45 46create type insert_test_type as (if1 int, if2 text[]); 47 48create table inserttest (f1 int, f2 int[], 49 f3 insert_test_type, f4 insert_test_type[]); 50 51insert into inserttest (f2[1], f2[2]) values (1,2); 52insert into inserttest (f2[1], f2[2]) values (3,4), (5,6); 53insert into inserttest (f2[1], f2[2]) select 7,8; 54insert into inserttest (f2[1], f2[2]) values (1,default); -- not supported 55 56insert into inserttest (f3.if1, f3.if2) values (1,array['foo']); 57insert into inserttest (f3.if1, f3.if2) values (1,'{foo}'), (2,'{bar}'); 58insert into inserttest (f3.if1, f3.if2) select 3, '{baz,quux}'; 59insert into inserttest (f3.if1, f3.if2) values (1,default); -- not supported 60 61insert into inserttest (f3.if2[1], f3.if2[2]) values ('foo', 'bar'); 62insert into inserttest (f3.if2[1], f3.if2[2]) values ('foo', 'bar'), ('baz', 'quux'); 63insert into inserttest (f3.if2[1], f3.if2[2]) select 'bear', 'beer'; 64 65insert into inserttest (f4[1].if2[1], f4[1].if2[2]) values ('foo', 'bar'); 66insert into inserttest (f4[1].if2[1], f4[1].if2[2]) values ('foo', 'bar'), ('baz', 'quux'); 67insert into inserttest (f4[1].if2[1], f4[1].if2[2]) select 'bear', 'beer'; 68 69select * from inserttest; 70 71-- also check reverse-listing 72create table inserttest2 (f1 bigint, f2 text); 73create rule irule1 as on insert to inserttest2 do also 74 insert into inserttest (f3.if2[1], f3.if2[2]) 75 values (new.f1,new.f2); 76create rule irule2 as on insert to inserttest2 do also 77 insert into inserttest (f4[1].if1, f4[1].if2[2]) 78 values (1,'fool'),(new.f1,new.f2); 79create rule irule3 as on insert to inserttest2 do also 80 insert into inserttest (f4[1].if1, f4[1].if2[2]) 81 select new.f1, new.f2; 82\d+ inserttest2 83 84drop table inserttest2; 85drop table inserttest; 86drop type insert_test_type; 87 88-- direct partition inserts should check partition bound constraint 89create table range_parted ( 90 a text, 91 b int 92) partition by range (a, (b+0)); 93 94-- no partitions, so fail 95insert into range_parted values ('a', 11); 96 97create table part1 partition of range_parted for values from ('a', 1) to ('a', 10); 98create table part2 partition of range_parted for values from ('a', 10) to ('a', 20); 99create table part3 partition of range_parted for values from ('b', 1) to ('b', 10); 100create table part4 partition of range_parted for values from ('b', 10) to ('b', 20); 101 102-- fail 103insert into part1 values ('a', 11); 104insert into part1 values ('b', 1); 105-- ok 106insert into part1 values ('a', 1); 107-- fail 108insert into part4 values ('b', 21); 109insert into part4 values ('a', 10); 110-- ok 111insert into part4 values ('b', 10); 112 113-- fail (partition key a has a NOT NULL constraint) 114insert into part1 values (null); 115-- fail (expression key (b+0) cannot be null either) 116insert into part1 values (1); 117 118create table list_parted ( 119 a text, 120 b int 121) partition by list (lower(a)); 122create table part_aa_bb partition of list_parted FOR VALUES IN ('aa', 'bb'); 123create table part_cc_dd partition of list_parted FOR VALUES IN ('cc', 'dd'); 124create table part_null partition of list_parted FOR VALUES IN (null); 125 126-- fail 127insert into part_aa_bb values ('cc', 1); 128insert into part_aa_bb values ('AAa', 1); 129insert into part_aa_bb values (null); 130-- ok 131insert into part_cc_dd values ('cC', 1); 132insert into part_null values (null, 0); 133 134-- check in case of multi-level partitioned table 135create table part_ee_ff partition of list_parted for values in ('ee', 'ff') partition by range (b); 136create table part_ee_ff1 partition of part_ee_ff for values from (1) to (10); 137create table part_ee_ff2 partition of part_ee_ff for values from (10) to (20); 138 139-- test default partition 140create table part_default partition of list_parted default; 141-- Negative test: a row, which would fit in other partition, does not fit 142-- default partition, even when inserted directly 143insert into part_default values ('aa', 2); 144insert into part_default values (null, 2); 145-- ok 146insert into part_default values ('Zz', 2); 147-- test if default partition works as expected for multi-level partitioned 148-- table as well as when default partition itself is further partitioned 149drop table part_default; 150create table part_xx_yy partition of list_parted for values in ('xx', 'yy') partition by list (a); 151create table part_xx_yy_p1 partition of part_xx_yy for values in ('xx'); 152create table part_xx_yy_defpart partition of part_xx_yy default; 153create table part_default partition of list_parted default partition by range(b); 154create table part_default_p1 partition of part_default for values from (20) to (30); 155create table part_default_p2 partition of part_default for values from (30) to (40); 156 157-- fail 158insert into part_ee_ff1 values ('EE', 11); 159insert into part_default_p2 values ('gg', 43); 160-- fail (even the parent's, ie, part_ee_ff's partition constraint applies) 161insert into part_ee_ff1 values ('cc', 1); 162insert into part_default values ('gg', 43); 163-- ok 164insert into part_ee_ff1 values ('ff', 1); 165insert into part_ee_ff2 values ('ff', 11); 166insert into part_default_p1 values ('cd', 25); 167insert into part_default_p2 values ('de', 35); 168insert into list_parted values ('ab', 21); 169insert into list_parted values ('xx', 1); 170insert into list_parted values ('yy', 2); 171select tableoid::regclass, * from list_parted; 172 173-- Check tuple routing for partitioned tables 174 175-- fail 176insert into range_parted values ('a', 0); 177-- ok 178insert into range_parted values ('a', 1); 179insert into range_parted values ('a', 10); 180-- fail 181insert into range_parted values ('a', 20); 182-- ok 183insert into range_parted values ('b', 1); 184insert into range_parted values ('b', 10); 185-- fail (partition key (b+0) is null) 186insert into range_parted values ('a'); 187 188-- Check default partition 189create table part_def partition of range_parted default; 190-- fail 191insert into part_def values ('b', 10); 192-- ok 193insert into part_def values ('c', 10); 194insert into range_parted values (null, null); 195insert into range_parted values ('a', null); 196insert into range_parted values (null, 19); 197insert into range_parted values ('b', 20); 198 199select tableoid::regclass, * from range_parted; 200-- ok 201insert into list_parted values (null, 1); 202insert into list_parted (a) values ('aA'); 203-- fail (partition of part_ee_ff not found in both cases) 204insert into list_parted values ('EE', 0); 205insert into part_ee_ff values ('EE', 0); 206-- ok 207insert into list_parted values ('EE', 1); 208insert into part_ee_ff values ('EE', 10); 209select tableoid::regclass, * from list_parted; 210 211-- some more tests to exercise tuple-routing with multi-level partitioning 212create table part_gg partition of list_parted for values in ('gg') partition by range (b); 213create table part_gg1 partition of part_gg for values from (minvalue) to (1); 214create table part_gg2 partition of part_gg for values from (1) to (10) partition by range (b); 215create table part_gg2_1 partition of part_gg2 for values from (1) to (5); 216create table part_gg2_2 partition of part_gg2 for values from (5) to (10); 217 218create table part_ee_ff3 partition of part_ee_ff for values from (20) to (30) partition by range (b); 219create table part_ee_ff3_1 partition of part_ee_ff3 for values from (20) to (25); 220create table part_ee_ff3_2 partition of part_ee_ff3 for values from (25) to (30); 221 222truncate list_parted; 223insert into list_parted values ('aa'), ('cc'); 224insert into list_parted select 'Ff', s.a from generate_series(1, 29) s(a); 225insert into list_parted select 'gg', s.a from generate_series(1, 9) s(a); 226insert into list_parted (b) values (1); 227select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_parted group by 1, 2 order by 1; 228 229-- direct partition inserts should check hash partition bound constraint 230 231-- Use hand-rolled hash functions and operator classes to get predictable 232-- result on different machines. The hash function for int4 simply returns 233-- the sum of the values passed to it and the one for text returns the length 234-- of the non-empty string value passed to it or 0. 235 236create or replace function part_hashint4_noop(value int4, seed int8) 237returns int8 as $$ 238select value + seed; 239$$ language sql immutable; 240 241create operator class part_test_int4_ops 242for type int4 243using hash as 244operator 1 =, 245function 2 part_hashint4_noop(int4, int8); 246 247create or replace function part_hashtext_length(value text, seed int8) 248RETURNS int8 AS $$ 249select length(coalesce(value, ''))::int8 250$$ language sql immutable; 251 252create operator class part_test_text_ops 253for type text 254using hash as 255operator 1 =, 256function 2 part_hashtext_length(text, int8); 257 258create table hash_parted ( 259 a int 260) partition by hash (a part_test_int4_ops); 261create table hpart0 partition of hash_parted for values with (modulus 4, remainder 0); 262create table hpart1 partition of hash_parted for values with (modulus 4, remainder 1); 263create table hpart2 partition of hash_parted for values with (modulus 4, remainder 2); 264create table hpart3 partition of hash_parted for values with (modulus 4, remainder 3); 265 266insert into hash_parted values(generate_series(1,10)); 267 268-- direct insert of values divisible by 4 - ok; 269insert into hpart0 values(12),(16); 270-- fail; 271insert into hpart0 values(11); 272-- 11 % 4 -> 3 remainder i.e. valid data for hpart3 partition 273insert into hpart3 values(11); 274 275-- view data 276select tableoid::regclass as part, a, a%4 as "remainder = a % 4" 277from hash_parted order by part; 278 279-- test \d+ output on a table which has both partitioned and unpartitioned 280-- partitions 281\d+ list_parted 282 283-- cleanup 284drop table range_parted, list_parted; 285drop table hash_parted; 286 287-- test that a default partition added as the first partition accepts any value 288-- including null 289create table list_parted (a int) partition by list (a); 290create table part_default partition of list_parted default; 291\d+ part_default 292insert into part_default values (null); 293insert into part_default values (1); 294insert into part_default values (-1); 295select tableoid::regclass, a from list_parted; 296-- cleanup 297drop table list_parted; 298 299-- more tests for certain multi-level partitioning scenarios 300create table mlparted (a int, b int) partition by range (a, b); 301create table mlparted1 (b int not null, a int not null) partition by range ((b+0)); 302create table mlparted11 (like mlparted1); 303alter table mlparted11 drop a; 304alter table mlparted11 add a int; 305alter table mlparted11 drop a; 306alter table mlparted11 add a int not null; 307-- attnum for key attribute 'a' is different in mlparted, mlparted1, and mlparted11 308select attrelid::regclass, attname, attnum 309from pg_attribute 310where attname = 'a' 311 and (attrelid = 'mlparted'::regclass 312 or attrelid = 'mlparted1'::regclass 313 or attrelid = 'mlparted11'::regclass) 314order by attrelid::regclass::text; 315 316alter table mlparted1 attach partition mlparted11 for values from (2) to (5); 317alter table mlparted attach partition mlparted1 for values from (1, 2) to (1, 10); 318 319-- check that "(1, 2)" is correctly routed to mlparted11. 320insert into mlparted values (1, 2); 321select tableoid::regclass, * from mlparted; 322 323-- check that proper message is shown after failure to route through mlparted1 324insert into mlparted (a, b) values (1, 5); 325 326truncate mlparted; 327alter table mlparted add constraint check_b check (b = 3); 328 329-- have a BR trigger modify the row such that the check_b is violated 330create function mlparted11_trig_fn() 331returns trigger AS 332$$ 333begin 334 NEW.b := 4; 335 return NEW; 336end; 337$$ 338language plpgsql; 339create trigger mlparted11_trig before insert ON mlparted11 340 for each row execute procedure mlparted11_trig_fn(); 341 342-- check that the correct row is shown when constraint check_b fails after 343-- "(1, 2)" is routed to mlparted11 (actually "(1, 4)" would be shown due 344-- to the BR trigger mlparted11_trig_fn) 345insert into mlparted values (1, 2); 346drop trigger mlparted11_trig on mlparted11; 347drop function mlparted11_trig_fn(); 348 349-- check that inserting into an internal partition successfully results in 350-- checking its partition constraint before inserting into the leaf partition 351-- selected by tuple-routing 352insert into mlparted1 (a, b) values (2, 3); 353 354-- check routing error through a list partitioned table when the key is null 355create table lparted_nonullpart (a int, b char) partition by list (b); 356create table lparted_nonullpart_a partition of lparted_nonullpart for values in ('a'); 357insert into lparted_nonullpart values (1); 358drop table lparted_nonullpart; 359 360-- check that RETURNING works correctly with tuple-routing 361alter table mlparted drop constraint check_b; 362create table mlparted12 partition of mlparted1 for values from (5) to (10); 363create table mlparted2 (b int not null, a int not null); 364alter table mlparted attach partition mlparted2 for values from (1, 10) to (1, 20); 365create table mlparted3 partition of mlparted for values from (1, 20) to (1, 30); 366create table mlparted4 (like mlparted); 367alter table mlparted4 drop a; 368alter table mlparted4 add a int not null; 369alter table mlparted attach partition mlparted4 for values from (1, 30) to (1, 40); 370with ins (a, b, c) as 371 (insert into mlparted (b, a) select s.a, 1 from generate_series(2, 39) s(a) returning tableoid::regclass, *) 372 select a, b, min(c), max(c) from ins group by a, b order by 1; 373 374alter table mlparted add c text; 375create table mlparted5 (c text, a int not null, b int not null) partition by list (c); 376create table mlparted5a (a int not null, c text, b int not null); 377alter table mlparted5 attach partition mlparted5a for values in ('a'); 378alter table mlparted attach partition mlparted5 for values from (1, 40) to (1, 50); 379alter table mlparted add constraint check_b check (a = 1 and b < 45); 380insert into mlparted values (1, 45, 'a'); 381create function mlparted5abrtrig_func() returns trigger as $$ begin new.c = 'b'; return new; end; $$ language plpgsql; 382create trigger mlparted5abrtrig before insert on mlparted5a for each row execute procedure mlparted5abrtrig_func(); 383insert into mlparted5 (a, b, c) values (1, 40, 'a'); 384drop table mlparted5; 385alter table mlparted drop constraint check_b; 386 387-- Check multi-level default partition 388create table mlparted_def partition of mlparted default partition by range(a); 389create table mlparted_def1 partition of mlparted_def for values from (40) to (50); 390create table mlparted_def2 partition of mlparted_def for values from (50) to (60); 391insert into mlparted values (40, 100); 392insert into mlparted_def1 values (42, 100); 393insert into mlparted_def2 values (54, 50); 394-- fail 395insert into mlparted values (70, 100); 396insert into mlparted_def1 values (52, 50); 397insert into mlparted_def2 values (34, 50); 398-- ok 399create table mlparted_defd partition of mlparted_def default; 400insert into mlparted values (70, 100); 401 402select tableoid::regclass, * from mlparted_def; 403 404-- Check multi-level tuple routing with attributes dropped from the 405-- top-most parent. First remove the last attribute. 406alter table mlparted add d int, add e int; 407alter table mlparted drop e; 408create table mlparted5 partition of mlparted 409 for values from (1, 40) to (1, 50) partition by range (c); 410create table mlparted5_ab partition of mlparted5 411 for values from ('a') to ('c') partition by list (c); 412-- This partitioned table should remain with no partitions. 413create table mlparted5_cd partition of mlparted5 414 for values from ('c') to ('e') partition by list (c); 415create table mlparted5_a partition of mlparted5_ab for values in ('a'); 416create table mlparted5_b (d int, b int, c text, a int); 417alter table mlparted5_ab attach partition mlparted5_b for values in ('b'); 418truncate mlparted; 419insert into mlparted values (1, 2, 'a', 1); 420insert into mlparted values (1, 40, 'a', 1); -- goes to mlparted5_a 421insert into mlparted values (1, 45, 'b', 1); -- goes to mlparted5_b 422insert into mlparted values (1, 45, 'c', 1); -- goes to mlparted5_cd, fails 423insert into mlparted values (1, 45, 'f', 1); -- goes to mlparted5, fails 424select tableoid::regclass, * from mlparted order by a, b, c, d; 425alter table mlparted drop d; 426truncate mlparted; 427-- Remove the before last attribute. 428alter table mlparted add e int, add d int; 429alter table mlparted drop e; 430insert into mlparted values (1, 2, 'a', 1); 431insert into mlparted values (1, 40, 'a', 1); -- goes to mlparted5_a 432insert into mlparted values (1, 45, 'b', 1); -- goes to mlparted5_b 433insert into mlparted values (1, 45, 'c', 1); -- goes to mlparted5_cd, fails 434insert into mlparted values (1, 45, 'f', 1); -- goes to mlparted5, fails 435select tableoid::regclass, * from mlparted order by a, b, c, d; 436alter table mlparted drop d; 437drop table mlparted5; 438 439-- check that message shown after failure to find a partition shows the 440-- appropriate key description (or none) in various situations 441create table key_desc (a int, b int) partition by list ((a+0)); 442create table key_desc_1 partition of key_desc for values in (1) partition by range (b); 443 444create user regress_insert_other_user; 445grant select (a) on key_desc_1 to regress_insert_other_user; 446grant insert on key_desc to regress_insert_other_user; 447 448set role regress_insert_other_user; 449-- no key description is shown 450insert into key_desc values (1, 1); 451 452reset role; 453grant select (b) on key_desc_1 to regress_insert_other_user; 454set role regress_insert_other_user; 455-- key description (b)=(1) is now shown 456insert into key_desc values (1, 1); 457 458-- key description is not shown if key contains expression 459insert into key_desc values (2, 1); 460reset role; 461revoke all on key_desc from regress_insert_other_user; 462revoke all on key_desc_1 from regress_insert_other_user; 463drop role regress_insert_other_user; 464drop table key_desc, key_desc_1; 465 466-- test minvalue/maxvalue restrictions 467create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c); 468create table mcrparted0 partition of mcrparted for values from (minvalue, 0, 0) to (1, maxvalue, maxvalue); 469create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue, minvalue); 470create table mcrparted4 partition of mcrparted for values from (21, minvalue, 0) to (30, 20, minvalue); 471 472-- check multi-column range partitioning expression enforces the same 473-- constraint as what tuple-routing would determine it to be 474create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, maxvalue, maxvalue); 475create table mcrparted1 partition of mcrparted for values from (2, 1, minvalue) to (10, 5, 10); 476create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue, maxvalue); 477create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10); 478create table mcrparted4 partition of mcrparted for values from (21, minvalue, minvalue) to (30, 20, maxvalue); 479create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (maxvalue, maxvalue, maxvalue); 480 481-- null not allowed in range partition 482insert into mcrparted values (null, null, null); 483 484-- routed to mcrparted0 485insert into mcrparted values (0, 1, 1); 486insert into mcrparted0 values (0, 1, 1); 487 488-- routed to mcparted1 489insert into mcrparted values (9, 1000, 1); 490insert into mcrparted1 values (9, 1000, 1); 491insert into mcrparted values (10, 5, -1); 492insert into mcrparted1 values (10, 5, -1); 493insert into mcrparted values (2, 1, 0); 494insert into mcrparted1 values (2, 1, 0); 495 496-- routed to mcparted2 497insert into mcrparted values (10, 6, 1000); 498insert into mcrparted2 values (10, 6, 1000); 499insert into mcrparted values (10, 1000, 1000); 500insert into mcrparted2 values (10, 1000, 1000); 501 502-- no partition exists, nor does mcrparted3 accept it 503insert into mcrparted values (11, 1, -1); 504insert into mcrparted3 values (11, 1, -1); 505 506-- routed to mcrparted5 507insert into mcrparted values (30, 21, 20); 508insert into mcrparted5 values (30, 21, 20); 509insert into mcrparted4 values (30, 21, 20); -- error 510 511-- check rows 512select tableoid::regclass::text, * from mcrparted order by 1; 513 514-- cleanup 515drop table mcrparted; 516 517-- check that a BR constraint can't make partition contain violating rows 518create table brtrigpartcon (a int, b text) partition by list (a); 519create table brtrigpartcon1 partition of brtrigpartcon for values in (1); 520create or replace function brtrigpartcon1trigf() returns trigger as $$begin new.a := 2; return new; end$$ language plpgsql; 521create trigger brtrigpartcon1trig before insert on brtrigpartcon1 for each row execute procedure brtrigpartcon1trigf(); 522insert into brtrigpartcon values (1, 'hi there'); 523insert into brtrigpartcon1 values (1, 'hi there'); 524 525-- check that the message shows the appropriate column description in a 526-- situation where the partitioned table is not the primary ModifyTable node 527create table inserttest3 (f1 text default 'foo', f2 text default 'bar', f3 int); 528create role regress_coldesc_role; 529grant insert on inserttest3 to regress_coldesc_role; 530grant insert on brtrigpartcon to regress_coldesc_role; 531revoke select on brtrigpartcon from regress_coldesc_role; 532set role regress_coldesc_role; 533with result as (insert into brtrigpartcon values (1, 'hi there') returning 1) 534 insert into inserttest3 (f3) select * from result; 535reset role; 536 537-- cleanup 538revoke all on inserttest3 from regress_coldesc_role; 539revoke all on brtrigpartcon from regress_coldesc_role; 540drop role regress_coldesc_role; 541drop table inserttest3; 542drop table brtrigpartcon; 543drop function brtrigpartcon1trigf(); 544 545-- check that "do nothing" BR triggers work with tuple-routing (this checks 546-- that estate->es_result_relation_info is appropriately set/reset for each 547-- routed tuple) 548create table donothingbrtrig_test (a int, b text) partition by list (a); 549create table donothingbrtrig_test1 (b text, a int); 550create table donothingbrtrig_test2 (c text, b text, a int); 551alter table donothingbrtrig_test2 drop column c; 552create or replace function donothingbrtrig_func() returns trigger as $$begin raise notice 'b: %', new.b; return NULL; end$$ language plpgsql; 553create trigger donothingbrtrig1 before insert on donothingbrtrig_test1 for each row execute procedure donothingbrtrig_func(); 554create trigger donothingbrtrig2 before insert on donothingbrtrig_test2 for each row execute procedure donothingbrtrig_func(); 555alter table donothingbrtrig_test attach partition donothingbrtrig_test1 for values in (1); 556alter table donothingbrtrig_test attach partition donothingbrtrig_test2 for values in (2); 557insert into donothingbrtrig_test values (1, 'foo'), (2, 'bar'); 558copy donothingbrtrig_test from stdout; 5591 baz 5602 qux 561\. 562select tableoid::regclass, * from donothingbrtrig_test; 563 564-- cleanup 565drop table donothingbrtrig_test; 566drop function donothingbrtrig_func(); 567 568-- check multi-column range partitioning with minvalue/maxvalue constraints 569create table mcrparted (a text, b int) partition by range(a, b); 570create table mcrparted1_lt_b partition of mcrparted for values from (minvalue, minvalue) to ('b', minvalue); 571create table mcrparted2_b partition of mcrparted for values from ('b', minvalue) to ('c', minvalue); 572create table mcrparted3_c_to_common partition of mcrparted for values from ('c', minvalue) to ('common', minvalue); 573create table mcrparted4_common_lt_0 partition of mcrparted for values from ('common', minvalue) to ('common', 0); 574create table mcrparted5_common_0_to_10 partition of mcrparted for values from ('common', 0) to ('common', 10); 575create table mcrparted6_common_ge_10 partition of mcrparted for values from ('common', 10) to ('common', maxvalue); 576create table mcrparted7_gt_common_lt_d partition of mcrparted for values from ('common', maxvalue) to ('d', minvalue); 577create table mcrparted8_ge_d partition of mcrparted for values from ('d', minvalue) to (maxvalue, maxvalue); 578 579\d+ mcrparted 580\d+ mcrparted1_lt_b 581\d+ mcrparted2_b 582\d+ mcrparted3_c_to_common 583\d+ mcrparted4_common_lt_0 584\d+ mcrparted5_common_0_to_10 585\d+ mcrparted6_common_ge_10 586\d+ mcrparted7_gt_common_lt_d 587\d+ mcrparted8_ge_d 588 589insert into mcrparted values ('aaa', 0), ('b', 0), ('bz', 10), ('c', -10), 590 ('comm', -10), ('common', -10), ('common', 0), ('common', 10), 591 ('commons', 0), ('d', -10), ('e', 0); 592select tableoid::regclass, * from mcrparted order by a, b; 593drop table mcrparted; 594 595-- check that wholerow vars in the RETURNING list work with partitioned tables 596create table returningwrtest (a int) partition by list (a); 597create table returningwrtest1 partition of returningwrtest for values in (1); 598insert into returningwrtest values (1) returning returningwrtest; 599 600-- check also that the wholerow vars in RETURNING list are converted as needed 601alter table returningwrtest add b text; 602create table returningwrtest2 (b text, c int, a int); 603alter table returningwrtest2 drop c; 604alter table returningwrtest attach partition returningwrtest2 for values in (2); 605insert into returningwrtest values (2, 'foo') returning returningwrtest; 606drop table returningwrtest; 607