-- -- insert with DEFAULT in the target_list -- create table inserttest (col1 int4, col2 int4 NOT NULL, col3 text default 'testing'); insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT, DEFAULT); ERROR: null value in column "col2" of relation "inserttest" violates not-null constraint DETAIL: Failing row contains (null, null, testing). insert into inserttest (col2, col3) values (3, DEFAULT); insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT); insert into inserttest values (DEFAULT, 5, 'test'); insert into inserttest values (DEFAULT, 7); select * from inserttest; col1 | col2 | col3 ------+------+--------- | 3 | testing | 5 | testing | 5 | test | 7 | testing (4 rows) -- -- insert with similar expression / target_list values (all fail) -- insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT); ERROR: INSERT has more target columns than expressions LINE 1: insert into inserttest (col1, col2, col3) values (DEFAULT, D... ^ insert into inserttest (col1, col2, col3) values (1, 2); ERROR: INSERT has more target columns than expressions LINE 1: insert into inserttest (col1, col2, col3) values (1, 2); ^ insert into inserttest (col1) values (1, 2); ERROR: INSERT has more expressions than target columns LINE 1: insert into inserttest (col1) values (1, 2); ^ insert into inserttest (col1) values (DEFAULT, DEFAULT); ERROR: INSERT has more expressions than target columns LINE 1: insert into inserttest (col1) values (DEFAULT, DEFAULT); ^ select * from inserttest; col1 | col2 | col3 ------+------+--------- | 3 | testing | 5 | testing | 5 | test | 7 | testing (4 rows) -- -- VALUES test -- insert into inserttest values(10, 20, '40'), (-1, 2, DEFAULT), ((select 2), (select i from (values(3)) as foo (i)), 'values are fun!'); select * from inserttest; col1 | col2 | col3 ------+------+----------------- | 3 | testing | 5 | testing | 5 | test | 7 | testing 10 | 20 | 40 -1 | 2 | testing 2 | 3 | values are fun! (7 rows) -- -- TOASTed value test -- insert into inserttest values(30, 50, repeat('x', 10000)); select col1, col2, char_length(col3) from inserttest; col1 | col2 | char_length ------+------+------------- | 3 | 7 | 5 | 7 | 5 | 4 | 7 | 7 10 | 20 | 2 -1 | 2 | 7 2 | 3 | 15 30 | 50 | 10000 (8 rows) drop table inserttest; -- -- tuple larger than fillfactor -- CREATE TABLE large_tuple_test (a int, b text) WITH (fillfactor = 10); ALTER TABLE large_tuple_test ALTER COLUMN b SET STORAGE plain; -- create page w/ free space in range [nearlyEmptyFreeSpace, MaxHeapTupleSize) INSERT INTO large_tuple_test (select 1, NULL); -- should still fit on the page INSERT INTO large_tuple_test (select 2, repeat('a', 1000)); SELECT pg_size_pretty(pg_relation_size('large_tuple_test'::regclass, 'main')); pg_size_pretty ---------------- 8192 bytes (1 row) -- add small record to the second page INSERT INTO large_tuple_test (select 3, NULL); -- now this tuple won't fit on the second page, but the insert should -- still succeed by extending the relation INSERT INTO large_tuple_test (select 4, repeat('a', 8126)); DROP TABLE large_tuple_test; -- -- check indirection (field/array assignment), cf bug #14265 -- -- these tests are aware that transformInsertStmt has 3 separate code paths -- create type insert_test_type as (if1 int, if2 text[]); create table inserttest (f1 int, f2 int[], f3 insert_test_type, f4 insert_test_type[]); insert into inserttest (f2[1], f2[2]) values (1,2); insert into inserttest (f2[1], f2[2]) values (3,4), (5,6); insert into inserttest (f2[1], f2[2]) select 7,8; insert into inserttest (f2[1], f2[2]) values (1,default); -- not supported ERROR: cannot set an array element to DEFAULT LINE 1: insert into inserttest (f2[1], f2[2]) values (1,default); ^ insert into inserttest (f3.if1, f3.if2) values (1,array['foo']); insert into inserttest (f3.if1, f3.if2) values (1,'{foo}'), (2,'{bar}'); insert into inserttest (f3.if1, f3.if2) select 3, '{baz,quux}'; insert into inserttest (f3.if1, f3.if2) values (1,default); -- not supported ERROR: cannot set a subfield to DEFAULT LINE 1: insert into inserttest (f3.if1, f3.if2) values (1,default); ^ insert into inserttest (f3.if2[1], f3.if2[2]) values ('foo', 'bar'); insert into inserttest (f3.if2[1], f3.if2[2]) values ('foo', 'bar'), ('baz', 'quux'); insert into inserttest (f3.if2[1], f3.if2[2]) select 'bear', 'beer'; insert into inserttest (f4[1].if2[1], f4[1].if2[2]) values ('foo', 'bar'); insert into inserttest (f4[1].if2[1], f4[1].if2[2]) values ('foo', 'bar'), ('baz', 'quux'); insert into inserttest (f4[1].if2[1], f4[1].if2[2]) select 'bear', 'beer'; select * from inserttest; f1 | f2 | f3 | f4 ----+-------+------------------+------------------------ | {1,2} | | | {3,4} | | | {5,6} | | | {7,8} | | | | (1,{foo}) | | | (1,{foo}) | | | (2,{bar}) | | | (3,"{baz,quux}") | | | (,"{foo,bar}") | | | (,"{foo,bar}") | | | (,"{baz,quux}") | | | (,"{bear,beer}") | | | | {"(,\"{foo,bar}\")"} | | | {"(,\"{foo,bar}\")"} | | | {"(,\"{baz,quux}\")"} | | | {"(,\"{bear,beer}\")"} (16 rows) -- also check reverse-listing create table inserttest2 (f1 bigint, f2 text); create rule irule1 as on insert to inserttest2 do also insert into inserttest (f3.if2[1], f3.if2[2]) values (new.f1,new.f2); create rule irule2 as on insert to inserttest2 do also insert into inserttest (f4[1].if1, f4[1].if2[2]) values (1,'fool'),(new.f1,new.f2); create rule irule3 as on insert to inserttest2 do also insert into inserttest (f4[1].if1, f4[1].if2[2]) select new.f1, new.f2; \d+ inserttest2 Table "public.inserttest2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+--------+-----------+----------+---------+----------+--------------+------------- f1 | bigint | | | | plain | | f2 | text | | | | extended | | Rules: irule1 AS ON INSERT TO inserttest2 DO INSERT INTO inserttest (f3.if2[1], f3.if2[2]) VALUES (new.f1, new.f2) irule2 AS ON INSERT TO inserttest2 DO INSERT INTO inserttest (f4[1].if1, f4[1].if2[2]) VALUES (1,'fool'::text), (new.f1,new.f2) irule3 AS ON INSERT TO inserttest2 DO INSERT INTO inserttest (f4[1].if1, f4[1].if2[2]) SELECT new.f1, new.f2 drop table inserttest2; drop table inserttest; -- Make the same tests with domains over the array and composite fields create domain insert_pos_ints as int[] check (value[1] > 0); create domain insert_test_domain as insert_test_type check ((value).if2[1] is not null); create table inserttesta (f1 int, f2 insert_pos_ints); create table inserttestb (f3 insert_test_domain, f4 insert_test_domain[]); insert into inserttesta (f2[1], f2[2]) values (1,2); insert into inserttesta (f2[1], f2[2]) values (3,4), (5,6); insert into inserttesta (f2[1], f2[2]) select 7,8; insert into inserttesta (f2[1], f2[2]) values (1,default); -- not supported ERROR: cannot set an array element to DEFAULT LINE 1: insert into inserttesta (f2[1], f2[2]) values (1,default); ^ insert into inserttesta (f2[1], f2[2]) values (0,2); ERROR: value for domain insert_pos_ints violates check constraint "insert_pos_ints_check" insert into inserttesta (f2[1], f2[2]) values (3,4), (0,6); ERROR: value for domain insert_pos_ints violates check constraint "insert_pos_ints_check" insert into inserttesta (f2[1], f2[2]) select 0,8; ERROR: value for domain insert_pos_ints violates check constraint "insert_pos_ints_check" insert into inserttestb (f3.if1, f3.if2) values (1,array['foo']); insert into inserttestb (f3.if1, f3.if2) values (1,'{foo}'), (2,'{bar}'); insert into inserttestb (f3.if1, f3.if2) select 3, '{baz,quux}'; insert into inserttestb (f3.if1, f3.if2) values (1,default); -- not supported ERROR: cannot set a subfield to DEFAULT LINE 1: insert into inserttestb (f3.if1, f3.if2) values (1,default); ^ insert into inserttestb (f3.if1, f3.if2) values (1,array[null]); ERROR: value for domain insert_test_domain violates check constraint "insert_test_domain_check" insert into inserttestb (f3.if1, f3.if2) values (1,'{null}'), (2,'{bar}'); ERROR: value for domain insert_test_domain violates check constraint "insert_test_domain_check" insert into inserttestb (f3.if1, f3.if2) select 3, '{null,quux}'; ERROR: value for domain insert_test_domain violates check constraint "insert_test_domain_check" insert into inserttestb (f3.if2[1], f3.if2[2]) values ('foo', 'bar'); insert into inserttestb (f3.if2[1], f3.if2[2]) values ('foo', 'bar'), ('baz', 'quux'); insert into inserttestb (f3.if2[1], f3.if2[2]) select 'bear', 'beer'; insert into inserttestb (f3, f4[1].if2[1], f4[1].if2[2]) values (row(1,'{x}'), 'foo', 'bar'); insert into inserttestb (f3, f4[1].if2[1], f4[1].if2[2]) values (row(1,'{x}'), 'foo', 'bar'), (row(2,'{y}'), 'baz', 'quux'); insert into inserttestb (f3, f4[1].if2[1], f4[1].if2[2]) select row(1,'{x}')::insert_test_domain, 'bear', 'beer'; select * from inserttesta; f1 | f2 ----+------- | {1,2} | {3,4} | {5,6} | {7,8} (4 rows) select * from inserttestb; f3 | f4 ------------------+------------------------ (1,{foo}) | (1,{foo}) | (2,{bar}) | (3,"{baz,quux}") | (,"{foo,bar}") | (,"{foo,bar}") | (,"{baz,quux}") | (,"{bear,beer}") | (1,{x}) | {"(,\"{foo,bar}\")"} (1,{x}) | {"(,\"{foo,bar}\")"} (2,{y}) | {"(,\"{baz,quux}\")"} (1,{x}) | {"(,\"{bear,beer}\")"} (12 rows) -- also check reverse-listing create table inserttest2 (f1 bigint, f2 text); create rule irule1 as on insert to inserttest2 do also insert into inserttestb (f3.if2[1], f3.if2[2]) values (new.f1,new.f2); create rule irule2 as on insert to inserttest2 do also insert into inserttestb (f4[1].if1, f4[1].if2[2]) values (1,'fool'),(new.f1,new.f2); create rule irule3 as on insert to inserttest2 do also insert into inserttestb (f4[1].if1, f4[1].if2[2]) select new.f1, new.f2; \d+ inserttest2 Table "public.inserttest2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+--------+-----------+----------+---------+----------+--------------+------------- f1 | bigint | | | | plain | | f2 | text | | | | extended | | Rules: irule1 AS ON INSERT TO inserttest2 DO INSERT INTO inserttestb (f3.if2[1], f3.if2[2]) VALUES (new.f1, new.f2) irule2 AS ON INSERT TO inserttest2 DO INSERT INTO inserttestb (f4[1].if1, f4[1].if2[2]) VALUES (1,'fool'::text), (new.f1,new.f2) irule3 AS ON INSERT TO inserttest2 DO INSERT INTO inserttestb (f4[1].if1, f4[1].if2[2]) SELECT new.f1, new.f2 drop table inserttest2; drop table inserttesta; drop table inserttestb; drop domain insert_pos_ints; drop domain insert_test_domain; -- Verify that multiple inserts to subfields of a domain-over-container -- check the domain constraints only on the finished value create domain insert_nnarray as int[] check (value[1] is not null and value[2] is not null); create domain insert_test_domain as insert_test_type check ((value).if1 is not null and (value).if2 is not null); create table inserttesta (f1 insert_nnarray); insert into inserttesta (f1[1]) values (1); -- fail ERROR: value for domain insert_nnarray violates check constraint "insert_nnarray_check" insert into inserttesta (f1[1], f1[2]) values (1, 2); create table inserttestb (f1 insert_test_domain); insert into inserttestb (f1.if1) values (1); -- fail ERROR: value for domain insert_test_domain violates check constraint "insert_test_domain_check" insert into inserttestb (f1.if1, f1.if2) values (1, '{foo}'); drop table inserttesta; drop table inserttestb; drop domain insert_nnarray; drop type insert_test_type cascade; NOTICE: drop cascades to type insert_test_domain -- direct partition inserts should check partition bound constraint create table range_parted ( a text, b int ) partition by range (a, (b+0)); -- no partitions, so fail insert into range_parted values ('a', 11); ERROR: no partition of relation "range_parted" found for row DETAIL: Partition key of the failing row contains (a, (b + 0)) = (a, 11). create table part1 partition of range_parted for values from ('a', 1) to ('a', 10); create table part2 partition of range_parted for values from ('a', 10) to ('a', 20); create table part3 partition of range_parted for values from ('b', 1) to ('b', 10); create table part4 partition of range_parted for values from ('b', 10) to ('b', 20); -- fail insert into part1 values ('a', 11); ERROR: new row for relation "part1" violates partition constraint DETAIL: Failing row contains (a, 11). insert into part1 values ('b', 1); ERROR: new row for relation "part1" violates partition constraint DETAIL: Failing row contains (b, 1). -- ok insert into part1 values ('a', 1); -- fail insert into part4 values ('b', 21); ERROR: new row for relation "part4" violates partition constraint DETAIL: Failing row contains (b, 21). insert into part4 values ('a', 10); ERROR: new row for relation "part4" violates partition constraint DETAIL: Failing row contains (a, 10). -- ok insert into part4 values ('b', 10); -- fail (partition key a has a NOT NULL constraint) insert into part1 values (null); ERROR: new row for relation "part1" violates partition constraint DETAIL: Failing row contains (null, null). -- fail (expression key (b+0) cannot be null either) insert into part1 values (1); ERROR: new row for relation "part1" violates partition constraint DETAIL: Failing row contains (1, null). create table list_parted ( a text, b int ) partition by list (lower(a)); create table part_aa_bb partition of list_parted FOR VALUES IN ('aa', 'bb'); create table part_cc_dd partition of list_parted FOR VALUES IN ('cc', 'dd'); create table part_null partition of list_parted FOR VALUES IN (null); -- fail insert into part_aa_bb values ('cc', 1); ERROR: new row for relation "part_aa_bb" violates partition constraint DETAIL: Failing row contains (cc, 1). insert into part_aa_bb values ('AAa', 1); ERROR: new row for relation "part_aa_bb" violates partition constraint DETAIL: Failing row contains (AAa, 1). insert into part_aa_bb values (null); ERROR: new row for relation "part_aa_bb" violates partition constraint DETAIL: Failing row contains (null, null). -- ok insert into part_cc_dd values ('cC', 1); insert into part_null values (null, 0); -- check in case of multi-level partitioned table create table part_ee_ff partition of list_parted for values in ('ee', 'ff') partition by range (b); create table part_ee_ff1 partition of part_ee_ff for values from (1) to (10); create table part_ee_ff2 partition of part_ee_ff for values from (10) to (20); -- test default partition create table part_default partition of list_parted default; -- Negative test: a row, which would fit in other partition, does not fit -- default partition, even when inserted directly insert into part_default values ('aa', 2); ERROR: new row for relation "part_default" violates partition constraint DETAIL: Failing row contains (aa, 2). insert into part_default values (null, 2); ERROR: new row for relation "part_default" violates partition constraint DETAIL: Failing row contains (null, 2). -- ok insert into part_default values ('Zz', 2); -- test if default partition works as expected for multi-level partitioned -- table as well as when default partition itself is further partitioned drop table part_default; create table part_xx_yy partition of list_parted for values in ('xx', 'yy') partition by list (a); create table part_xx_yy_p1 partition of part_xx_yy for values in ('xx'); create table part_xx_yy_defpart partition of part_xx_yy default; create table part_default partition of list_parted default partition by range(b); create table part_default_p1 partition of part_default for values from (20) to (30); create table part_default_p2 partition of part_default for values from (30) to (40); -- fail insert into part_ee_ff1 values ('EE', 11); ERROR: new row for relation "part_ee_ff1" violates partition constraint DETAIL: Failing row contains (EE, 11). insert into part_default_p2 values ('gg', 43); ERROR: new row for relation "part_default_p2" violates partition constraint DETAIL: Failing row contains (gg, 43). -- fail (even the parent's, ie, part_ee_ff's partition constraint applies) insert into part_ee_ff1 values ('cc', 1); ERROR: new row for relation "part_ee_ff1" violates partition constraint DETAIL: Failing row contains (cc, 1). insert into part_default values ('gg', 43); ERROR: no partition of relation "part_default" found for row DETAIL: Partition key of the failing row contains (b) = (43). -- ok insert into part_ee_ff1 values ('ff', 1); insert into part_ee_ff2 values ('ff', 11); insert into part_default_p1 values ('cd', 25); insert into part_default_p2 values ('de', 35); insert into list_parted values ('ab', 21); insert into list_parted values ('xx', 1); insert into list_parted values ('yy', 2); select tableoid::regclass, * from list_parted; tableoid | a | b --------------------+----+---- part_cc_dd | cC | 1 part_ee_ff1 | ff | 1 part_ee_ff2 | ff | 11 part_xx_yy_p1 | xx | 1 part_xx_yy_defpart | yy | 2 part_null | | 0 part_default_p1 | cd | 25 part_default_p1 | ab | 21 part_default_p2 | de | 35 (9 rows) -- Check tuple routing for partitioned tables -- fail insert into range_parted values ('a', 0); ERROR: no partition of relation "range_parted" found for row DETAIL: Partition key of the failing row contains (a, (b + 0)) = (a, 0). -- ok insert into range_parted values ('a', 1); insert into range_parted values ('a', 10); -- fail insert into range_parted values ('a', 20); ERROR: no partition of relation "range_parted" found for row DETAIL: Partition key of the failing row contains (a, (b + 0)) = (a, 20). -- ok insert into range_parted values ('b', 1); insert into range_parted values ('b', 10); -- fail (partition key (b+0) is null) insert into range_parted values ('a'); ERROR: no partition of relation "range_parted" found for row DETAIL: Partition key of the failing row contains (a, (b + 0)) = (a, null). -- Check default partition create table part_def partition of range_parted default; -- fail insert into part_def values ('b', 10); ERROR: new row for relation "part_def" violates partition constraint DETAIL: Failing row contains (b, 10). -- ok insert into part_def values ('c', 10); insert into range_parted values (null, null); insert into range_parted values ('a', null); insert into range_parted values (null, 19); insert into range_parted values ('b', 20); select tableoid::regclass, * from range_parted; tableoid | a | b ----------+---+---- part1 | a | 1 part1 | a | 1 part2 | a | 10 part3 | b | 1 part4 | b | 10 part4 | b | 10 part_def | c | 10 part_def | | part_def | a | part_def | | 19 part_def | b | 20 (11 rows) -- ok insert into list_parted values (null, 1); insert into list_parted (a) values ('aA'); -- fail (partition of part_ee_ff not found in both cases) insert into list_parted values ('EE', 0); ERROR: no partition of relation "part_ee_ff" found for row DETAIL: Partition key of the failing row contains (b) = (0). insert into part_ee_ff values ('EE', 0); ERROR: no partition of relation "part_ee_ff" found for row DETAIL: Partition key of the failing row contains (b) = (0). -- ok insert into list_parted values ('EE', 1); insert into part_ee_ff values ('EE', 10); select tableoid::regclass, * from list_parted; tableoid | a | b --------------------+----+---- part_aa_bb | aA | part_cc_dd | cC | 1 part_ee_ff1 | ff | 1 part_ee_ff1 | EE | 1 part_ee_ff2 | ff | 11 part_ee_ff2 | EE | 10 part_xx_yy_p1 | xx | 1 part_xx_yy_defpart | yy | 2 part_null | | 0 part_null | | 1 part_default_p1 | cd | 25 part_default_p1 | ab | 21 part_default_p2 | de | 35 (13 rows) -- some more tests to exercise tuple-routing with multi-level partitioning create table part_gg partition of list_parted for values in ('gg') partition by range (b); create table part_gg1 partition of part_gg for values from (minvalue) to (1); create table part_gg2 partition of part_gg for values from (1) to (10) partition by range (b); create table part_gg2_1 partition of part_gg2 for values from (1) to (5); create table part_gg2_2 partition of part_gg2 for values from (5) to (10); create table part_ee_ff3 partition of part_ee_ff for values from (20) to (30) partition by range (b); create table part_ee_ff3_1 partition of part_ee_ff3 for values from (20) to (25); create table part_ee_ff3_2 partition of part_ee_ff3 for values from (25) to (30); truncate list_parted; insert into list_parted values ('aa'), ('cc'); insert into list_parted select 'Ff', s.a from generate_series(1, 29) s(a); insert into list_parted select 'gg', s.a from generate_series(1, 9) s(a); insert into list_parted (b) values (1); select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_parted group by 1, 2 order by 1; tableoid | a | min_b | max_b ---------------+----+-------+------- part_aa_bb | aa | | part_cc_dd | cc | | part_ee_ff1 | Ff | 1 | 9 part_ee_ff2 | Ff | 10 | 19 part_ee_ff3_1 | Ff | 20 | 24 part_ee_ff3_2 | Ff | 25 | 29 part_gg2_1 | gg | 1 | 4 part_gg2_2 | gg | 5 | 9 part_null | | 1 | 1 (9 rows) -- direct partition inserts should check hash partition bound constraint create table hash_parted ( a int ) partition by hash (a part_test_int4_ops); create table hpart0 partition of hash_parted for values with (modulus 4, remainder 0); create table hpart1 partition of hash_parted for values with (modulus 4, remainder 1); create table hpart2 partition of hash_parted for values with (modulus 4, remainder 2); create table hpart3 partition of hash_parted for values with (modulus 4, remainder 3); insert into hash_parted values(generate_series(1,10)); -- direct insert of values divisible by 4 - ok; insert into hpart0 values(12),(16); -- fail; insert into hpart0 values(11); ERROR: new row for relation "hpart0" violates partition constraint DETAIL: Failing row contains (11). -- 11 % 4 -> 3 remainder i.e. valid data for hpart3 partition insert into hpart3 values(11); -- view data select tableoid::regclass as part, a, a%4 as "remainder = a % 4" from hash_parted order by part; part | a | remainder = a % 4 --------+----+------------------- hpart0 | 4 | 0 hpart0 | 8 | 0 hpart0 | 12 | 0 hpart0 | 16 | 0 hpart1 | 1 | 1 hpart1 | 5 | 1 hpart1 | 9 | 1 hpart2 | 2 | 2 hpart2 | 6 | 2 hpart2 | 10 | 2 hpart3 | 3 | 3 hpart3 | 7 | 3 hpart3 | 11 | 3 (13 rows) -- test \d+ output on a table which has both partitioned and unpartitioned -- partitions \d+ list_parted Partitioned table "public.list_parted" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- a | text | | | | extended | | b | integer | | | | plain | | Partition key: LIST (lower(a)) Partitions: part_aa_bb FOR VALUES IN ('aa', 'bb'), part_cc_dd FOR VALUES IN ('cc', 'dd'), part_ee_ff FOR VALUES IN ('ee', 'ff'), PARTITIONED, part_gg FOR VALUES IN ('gg'), PARTITIONED, part_null FOR VALUES IN (NULL), part_xx_yy FOR VALUES IN ('xx', 'yy'), PARTITIONED, part_default DEFAULT, PARTITIONED -- cleanup drop table range_parted, list_parted; drop table hash_parted; -- test that a default partition added as the first partition accepts any value -- including null create table list_parted (a int) partition by list (a); create table part_default partition of list_parted default; \d+ part_default Table "public.part_default" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- a | integer | | | | plain | | Partition of: list_parted DEFAULT No partition constraint insert into part_default values (null); insert into part_default values (1); insert into part_default values (-1); select tableoid::regclass, a from list_parted; tableoid | a --------------+---- part_default | part_default | 1 part_default | -1 (3 rows) -- cleanup drop table list_parted; -- more tests for certain multi-level partitioning scenarios create table mlparted (a int, b int) partition by range (a, b); create table mlparted1 (b int not null, a int not null) partition by range ((b+0)); create table mlparted11 (like mlparted1); alter table mlparted11 drop a; alter table mlparted11 add a int; alter table mlparted11 drop a; alter table mlparted11 add a int not null; -- attnum for key attribute 'a' is different in mlparted, mlparted1, and mlparted11 select attrelid::regclass, attname, attnum from pg_attribute where attname = 'a' and (attrelid = 'mlparted'::regclass or attrelid = 'mlparted1'::regclass or attrelid = 'mlparted11'::regclass) order by attrelid::regclass::text; attrelid | attname | attnum ------------+---------+-------- mlparted | a | 1 mlparted1 | a | 2 mlparted11 | a | 4 (3 rows) alter table mlparted1 attach partition mlparted11 for values from (2) to (5); alter table mlparted attach partition mlparted1 for values from (1, 2) to (1, 10); -- check that "(1, 2)" is correctly routed to mlparted11. insert into mlparted values (1, 2); select tableoid::regclass, * from mlparted; tableoid | a | b ------------+---+--- mlparted11 | 1 | 2 (1 row) -- check that proper message is shown after failure to route through mlparted1 insert into mlparted (a, b) values (1, 5); ERROR: no partition of relation "mlparted1" found for row DETAIL: Partition key of the failing row contains ((b + 0)) = (5). truncate mlparted; alter table mlparted add constraint check_b check (b = 3); -- have a BR trigger modify the row such that the check_b is violated create function mlparted11_trig_fn() returns trigger AS $$ begin NEW.b := 4; return NEW; end; $$ language plpgsql; create trigger mlparted11_trig before insert ON mlparted11 for each row execute procedure mlparted11_trig_fn(); -- check that the correct row is shown when constraint check_b fails after -- "(1, 2)" is routed to mlparted11 (actually "(1, 4)" would be shown due -- to the BR trigger mlparted11_trig_fn) insert into mlparted values (1, 2); ERROR: new row for relation "mlparted11" violates check constraint "check_b" DETAIL: Failing row contains (1, 4). drop trigger mlparted11_trig on mlparted11; drop function mlparted11_trig_fn(); -- check that inserting into an internal partition successfully results in -- checking its partition constraint before inserting into the leaf partition -- selected by tuple-routing insert into mlparted1 (a, b) values (2, 3); ERROR: new row for relation "mlparted1" violates partition constraint DETAIL: Failing row contains (3, 2). -- check routing error through a list partitioned table when the key is null create table lparted_nonullpart (a int, b char) partition by list (b); create table lparted_nonullpart_a partition of lparted_nonullpart for values in ('a'); insert into lparted_nonullpart values (1); ERROR: no partition of relation "lparted_nonullpart" found for row DETAIL: Partition key of the failing row contains (b) = (null). drop table lparted_nonullpart; -- check that RETURNING works correctly with tuple-routing alter table mlparted drop constraint check_b; create table mlparted12 partition of mlparted1 for values from (5) to (10); create table mlparted2 (b int not null, a int not null); alter table mlparted attach partition mlparted2 for values from (1, 10) to (1, 20); create table mlparted3 partition of mlparted for values from (1, 20) to (1, 30); create table mlparted4 (like mlparted); alter table mlparted4 drop a; alter table mlparted4 add a int not null; alter table mlparted attach partition mlparted4 for values from (1, 30) to (1, 40); with ins (a, b, c) as (insert into mlparted (b, a) select s.a, 1 from generate_series(2, 39) s(a) returning tableoid::regclass, *) select a, b, min(c), max(c) from ins group by a, b order by 1; a | b | min | max ------------+---+-----+----- mlparted11 | 1 | 2 | 4 mlparted12 | 1 | 5 | 9 mlparted2 | 1 | 10 | 19 mlparted3 | 1 | 20 | 29 mlparted4 | 1 | 30 | 39 (5 rows) alter table mlparted add c text; create table mlparted5 (c text, a int not null, b int not null) partition by list (c); create table mlparted5a (a int not null, c text, b int not null); alter table mlparted5 attach partition mlparted5a for values in ('a'); alter table mlparted attach partition mlparted5 for values from (1, 40) to (1, 50); alter table mlparted add constraint check_b check (a = 1 and b < 45); insert into mlparted values (1, 45, 'a'); ERROR: new row for relation "mlparted5a" violates check constraint "check_b" DETAIL: Failing row contains (1, 45, a). create function mlparted5abrtrig_func() returns trigger as $$ begin new.c = 'b'; return new; end; $$ language plpgsql; create trigger mlparted5abrtrig before insert on mlparted5a for each row execute procedure mlparted5abrtrig_func(); insert into mlparted5 (a, b, c) values (1, 40, 'a'); ERROR: new row for relation "mlparted5a" violates partition constraint DETAIL: Failing row contains (b, 1, 40). drop table mlparted5; alter table mlparted drop constraint check_b; -- Check multi-level default partition create table mlparted_def partition of mlparted default partition by range(a); create table mlparted_def1 partition of mlparted_def for values from (40) to (50); create table mlparted_def2 partition of mlparted_def for values from (50) to (60); insert into mlparted values (40, 100); insert into mlparted_def1 values (42, 100); insert into mlparted_def2 values (54, 50); -- fail insert into mlparted values (70, 100); ERROR: no partition of relation "mlparted_def" found for row DETAIL: Partition key of the failing row contains (a) = (70). insert into mlparted_def1 values (52, 50); ERROR: new row for relation "mlparted_def1" violates partition constraint DETAIL: Failing row contains (52, 50, null). insert into mlparted_def2 values (34, 50); ERROR: new row for relation "mlparted_def2" violates partition constraint DETAIL: Failing row contains (34, 50, null). -- ok create table mlparted_defd partition of mlparted_def default; insert into mlparted values (70, 100); select tableoid::regclass, * from mlparted_def; tableoid | a | b | c ---------------+----+-----+--- mlparted_def1 | 40 | 100 | mlparted_def1 | 42 | 100 | mlparted_def2 | 54 | 50 | mlparted_defd | 70 | 100 | (4 rows) -- Check multi-level tuple routing with attributes dropped from the -- top-most parent. First remove the last attribute. alter table mlparted add d int, add e int; alter table mlparted drop e; create table mlparted5 partition of mlparted for values from (1, 40) to (1, 50) partition by range (c); create table mlparted5_ab partition of mlparted5 for values from ('a') to ('c') partition by list (c); -- This partitioned table should remain with no partitions. create table mlparted5_cd partition of mlparted5 for values from ('c') to ('e') partition by list (c); create table mlparted5_a partition of mlparted5_ab for values in ('a'); create table mlparted5_b (d int, b int, c text, a int); alter table mlparted5_ab attach partition mlparted5_b for values in ('b'); truncate mlparted; insert into mlparted values (1, 2, 'a', 1); insert into mlparted values (1, 40, 'a', 1); -- goes to mlparted5_a insert into mlparted values (1, 45, 'b', 1); -- goes to mlparted5_b insert into mlparted values (1, 45, 'c', 1); -- goes to mlparted5_cd, fails ERROR: no partition of relation "mlparted5_cd" found for row DETAIL: Partition key of the failing row contains (c) = (c). insert into mlparted values (1, 45, 'f', 1); -- goes to mlparted5, fails ERROR: no partition of relation "mlparted5" found for row DETAIL: Partition key of the failing row contains (c) = (f). select tableoid::regclass, * from mlparted order by a, b, c, d; tableoid | a | b | c | d -------------+---+----+---+--- mlparted11 | 1 | 2 | a | 1 mlparted5_a | 1 | 40 | a | 1 mlparted5_b | 1 | 45 | b | 1 (3 rows) alter table mlparted drop d; truncate mlparted; -- Remove the before last attribute. alter table mlparted add e int, add d int; alter table mlparted drop e; insert into mlparted values (1, 2, 'a', 1); insert into mlparted values (1, 40, 'a', 1); -- goes to mlparted5_a insert into mlparted values (1, 45, 'b', 1); -- goes to mlparted5_b insert into mlparted values (1, 45, 'c', 1); -- goes to mlparted5_cd, fails ERROR: no partition of relation "mlparted5_cd" found for row DETAIL: Partition key of the failing row contains (c) = (c). insert into mlparted values (1, 45, 'f', 1); -- goes to mlparted5, fails ERROR: no partition of relation "mlparted5" found for row DETAIL: Partition key of the failing row contains (c) = (f). select tableoid::regclass, * from mlparted order by a, b, c, d; tableoid | a | b | c | d -------------+---+----+---+--- mlparted11 | 1 | 2 | a | 1 mlparted5_a | 1 | 40 | a | 1 mlparted5_b | 1 | 45 | b | 1 (3 rows) alter table mlparted drop d; drop table mlparted5; -- check that message shown after failure to find a partition shows the -- appropriate key description (or none) in various situations create table key_desc (a int, b int) partition by list ((a+0)); create table key_desc_1 partition of key_desc for values in (1) partition by range (b); create user regress_insert_other_user; grant select (a) on key_desc_1 to regress_insert_other_user; grant insert on key_desc to regress_insert_other_user; set role regress_insert_other_user; -- no key description is shown insert into key_desc values (1, 1); ERROR: no partition of relation "key_desc_1" found for row reset role; grant select (b) on key_desc_1 to regress_insert_other_user; set role regress_insert_other_user; -- key description (b)=(1) is now shown insert into key_desc values (1, 1); ERROR: no partition of relation "key_desc_1" found for row DETAIL: Partition key of the failing row contains (b) = (1). -- key description is not shown if key contains expression insert into key_desc values (2, 1); ERROR: no partition of relation "key_desc" found for row reset role; revoke all on key_desc from regress_insert_other_user; revoke all on key_desc_1 from regress_insert_other_user; drop role regress_insert_other_user; drop table key_desc, key_desc_1; -- test minvalue/maxvalue restrictions create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c); create table mcrparted0 partition of mcrparted for values from (minvalue, 0, 0) to (1, maxvalue, maxvalue); ERROR: every bound following MINVALUE must also be MINVALUE LINE 1: ...partition of mcrparted for values from (minvalue, 0, 0) to (... ^ create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue, minvalue); ERROR: every bound following MAXVALUE must also be MAXVALUE LINE 1: ...r values from (10, 6, minvalue) to (10, maxvalue, minvalue); ^ create table mcrparted4 partition of mcrparted for values from (21, minvalue, 0) to (30, 20, minvalue); ERROR: every bound following MINVALUE must also be MINVALUE LINE 1: ...ition of mcrparted for values from (21, minvalue, 0) to (30,... ^ -- check multi-column range partitioning expression enforces the same -- constraint as what tuple-routing would determine it to be create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, maxvalue, maxvalue); create table mcrparted1 partition of mcrparted for values from (2, 1, minvalue) to (10, 5, 10); create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue, maxvalue); create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10); create table mcrparted4 partition of mcrparted for values from (21, minvalue, minvalue) to (30, 20, maxvalue); create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (maxvalue, maxvalue, maxvalue); -- null not allowed in range partition insert into mcrparted values (null, null, null); ERROR: no partition of relation "mcrparted" found for row DETAIL: Partition key of the failing row contains (a, abs(b), c) = (null, null, null). -- routed to mcrparted0 insert into mcrparted values (0, 1, 1); insert into mcrparted0 values (0, 1, 1); -- routed to mcparted1 insert into mcrparted values (9, 1000, 1); insert into mcrparted1 values (9, 1000, 1); insert into mcrparted values (10, 5, -1); insert into mcrparted1 values (10, 5, -1); insert into mcrparted values (2, 1, 0); insert into mcrparted1 values (2, 1, 0); -- routed to mcparted2 insert into mcrparted values (10, 6, 1000); insert into mcrparted2 values (10, 6, 1000); insert into mcrparted values (10, 1000, 1000); insert into mcrparted2 values (10, 1000, 1000); -- no partition exists, nor does mcrparted3 accept it insert into mcrparted values (11, 1, -1); ERROR: no partition of relation "mcrparted" found for row DETAIL: Partition key of the failing row contains (a, abs(b), c) = (11, 1, -1). insert into mcrparted3 values (11, 1, -1); ERROR: new row for relation "mcrparted3" violates partition constraint DETAIL: Failing row contains (11, 1, -1). -- routed to mcrparted5 insert into mcrparted values (30, 21, 20); insert into mcrparted5 values (30, 21, 20); insert into mcrparted4 values (30, 21, 20); -- error ERROR: new row for relation "mcrparted4" violates partition constraint DETAIL: Failing row contains (30, 21, 20). -- check rows select tableoid::regclass::text, * from mcrparted order by 1; tableoid | a | b | c ------------+----+------+------ mcrparted0 | 0 | 1 | 1 mcrparted0 | 0 | 1 | 1 mcrparted1 | 9 | 1000 | 1 mcrparted1 | 9 | 1000 | 1 mcrparted1 | 10 | 5 | -1 mcrparted1 | 10 | 5 | -1 mcrparted1 | 2 | 1 | 0 mcrparted1 | 2 | 1 | 0 mcrparted2 | 10 | 6 | 1000 mcrparted2 | 10 | 6 | 1000 mcrparted2 | 10 | 1000 | 1000 mcrparted2 | 10 | 1000 | 1000 mcrparted5 | 30 | 21 | 20 mcrparted5 | 30 | 21 | 20 (14 rows) -- cleanup drop table mcrparted; -- check that a BR constraint can't make partition contain violating rows create table brtrigpartcon (a int, b text) partition by list (a); create table brtrigpartcon1 partition of brtrigpartcon for values in (1); create or replace function brtrigpartcon1trigf() returns trigger as $$begin new.a := 2; return new; end$$ language plpgsql; create trigger brtrigpartcon1trig before insert on brtrigpartcon1 for each row execute procedure brtrigpartcon1trigf(); insert into brtrigpartcon values (1, 'hi there'); ERROR: new row for relation "brtrigpartcon1" violates partition constraint DETAIL: Failing row contains (2, hi there). insert into brtrigpartcon1 values (1, 'hi there'); ERROR: new row for relation "brtrigpartcon1" violates partition constraint DETAIL: Failing row contains (2, hi there). -- check that the message shows the appropriate column description in a -- situation where the partitioned table is not the primary ModifyTable node create table inserttest3 (f1 text default 'foo', f2 text default 'bar', f3 int); create role regress_coldesc_role; grant insert on inserttest3 to regress_coldesc_role; grant insert on brtrigpartcon to regress_coldesc_role; revoke select on brtrigpartcon from regress_coldesc_role; set role regress_coldesc_role; with result as (insert into brtrigpartcon values (1, 'hi there') returning 1) insert into inserttest3 (f3) select * from result; ERROR: new row for relation "brtrigpartcon1" violates partition constraint DETAIL: Failing row contains (a, b) = (2, hi there). reset role; -- cleanup revoke all on inserttest3 from regress_coldesc_role; revoke all on brtrigpartcon from regress_coldesc_role; drop role regress_coldesc_role; drop table inserttest3; drop table brtrigpartcon; drop function brtrigpartcon1trigf(); -- check that "do nothing" BR triggers work with tuple-routing create table donothingbrtrig_test (a int, b text) partition by list (a); create table donothingbrtrig_test1 (b text, a int); create table donothingbrtrig_test2 (c text, b text, a int); alter table donothingbrtrig_test2 drop column c; create or replace function donothingbrtrig_func() returns trigger as $$begin raise notice 'b: %', new.b; return NULL; end$$ language plpgsql; create trigger donothingbrtrig1 before insert on donothingbrtrig_test1 for each row execute procedure donothingbrtrig_func(); create trigger donothingbrtrig2 before insert on donothingbrtrig_test2 for each row execute procedure donothingbrtrig_func(); alter table donothingbrtrig_test attach partition donothingbrtrig_test1 for values in (1); alter table donothingbrtrig_test attach partition donothingbrtrig_test2 for values in (2); insert into donothingbrtrig_test values (1, 'foo'), (2, 'bar'); NOTICE: b: foo NOTICE: b: bar copy donothingbrtrig_test from stdout; NOTICE: b: baz NOTICE: b: qux select tableoid::regclass, * from donothingbrtrig_test; tableoid | a | b ----------+---+--- (0 rows) -- cleanup drop table donothingbrtrig_test; drop function donothingbrtrig_func(); -- check multi-column range partitioning with minvalue/maxvalue constraints create table mcrparted (a text, b int) partition by range(a, b); create table mcrparted1_lt_b partition of mcrparted for values from (minvalue, minvalue) to ('b', minvalue); create table mcrparted2_b partition of mcrparted for values from ('b', minvalue) to ('c', minvalue); create table mcrparted3_c_to_common partition of mcrparted for values from ('c', minvalue) to ('common', minvalue); create table mcrparted4_common_lt_0 partition of mcrparted for values from ('common', minvalue) to ('common', 0); create table mcrparted5_common_0_to_10 partition of mcrparted for values from ('common', 0) to ('common', 10); create table mcrparted6_common_ge_10 partition of mcrparted for values from ('common', 10) to ('common', maxvalue); create table mcrparted7_gt_common_lt_d partition of mcrparted for values from ('common', maxvalue) to ('d', minvalue); create table mcrparted8_ge_d partition of mcrparted for values from ('d', minvalue) to (maxvalue, maxvalue); \d+ mcrparted Partitioned table "public.mcrparted" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- a | text | | | | extended | | b | integer | | | | plain | | Partition key: RANGE (a, b) Partitions: mcrparted1_lt_b FOR VALUES FROM (MINVALUE, MINVALUE) TO ('b', MINVALUE), mcrparted2_b FOR VALUES FROM ('b', MINVALUE) TO ('c', MINVALUE), mcrparted3_c_to_common FOR VALUES FROM ('c', MINVALUE) TO ('common', MINVALUE), mcrparted4_common_lt_0 FOR VALUES FROM ('common', MINVALUE) TO ('common', 0), mcrparted5_common_0_to_10 FOR VALUES FROM ('common', 0) TO ('common', 10), mcrparted6_common_ge_10 FOR VALUES FROM ('common', 10) TO ('common', MAXVALUE), mcrparted7_gt_common_lt_d FOR VALUES FROM ('common', MAXVALUE) TO ('d', MINVALUE), mcrparted8_ge_d FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE, MAXVALUE) \d+ mcrparted1_lt_b Table "public.mcrparted1_lt_b" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- a | text | | | | extended | | b | integer | | | | plain | | Partition of: mcrparted FOR VALUES FROM (MINVALUE, MINVALUE) TO ('b', MINVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a < 'b'::text)) \d+ mcrparted2_b Table "public.mcrparted2_b" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- a | text | | | | extended | | b | integer | | | | plain | | Partition of: mcrparted FOR VALUES FROM ('b', MINVALUE) TO ('c', MINVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'b'::text) AND (a < 'c'::text)) \d+ mcrparted3_c_to_common Table "public.mcrparted3_c_to_common" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- a | text | | | | extended | | b | integer | | | | plain | | Partition of: mcrparted FOR VALUES FROM ('c', MINVALUE) TO ('common', MINVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'c'::text) AND (a < 'common'::text)) \d+ mcrparted4_common_lt_0 Table "public.mcrparted4_common_lt_0" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- a | text | | | | extended | | b | integer | | | | plain | | Partition of: mcrparted FOR VALUES FROM ('common', MINVALUE) TO ('common', 0) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b < 0)) \d+ mcrparted5_common_0_to_10 Table "public.mcrparted5_common_0_to_10" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- a | text | | | | extended | | b | integer | | | | plain | | Partition of: mcrparted FOR VALUES FROM ('common', 0) TO ('common', 10) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b >= 0) AND (b < 10)) \d+ mcrparted6_common_ge_10 Table "public.mcrparted6_common_ge_10" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- a | text | | | | extended | | b | integer | | | | plain | | Partition of: mcrparted FOR VALUES FROM ('common', 10) TO ('common', MAXVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b >= 10)) \d+ mcrparted7_gt_common_lt_d Table "public.mcrparted7_gt_common_lt_d" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- a | text | | | | extended | | b | integer | | | | plain | | Partition of: mcrparted FOR VALUES FROM ('common', MAXVALUE) TO ('d', MINVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a > 'common'::text) AND (a < 'd'::text)) \d+ mcrparted8_ge_d Table "public.mcrparted8_ge_d" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- a | text | | | | extended | | b | integer | | | | plain | | Partition of: mcrparted FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE, MAXVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'd'::text)) insert into mcrparted values ('aaa', 0), ('b', 0), ('bz', 10), ('c', -10), ('comm', -10), ('common', -10), ('common', 0), ('common', 10), ('commons', 0), ('d', -10), ('e', 0); select tableoid::regclass, * from mcrparted order by a, b; tableoid | a | b ---------------------------+---------+----- mcrparted1_lt_b | aaa | 0 mcrparted2_b | b | 0 mcrparted2_b | bz | 10 mcrparted3_c_to_common | c | -10 mcrparted3_c_to_common | comm | -10 mcrparted4_common_lt_0 | common | -10 mcrparted5_common_0_to_10 | common | 0 mcrparted6_common_ge_10 | common | 10 mcrparted7_gt_common_lt_d | commons | 0 mcrparted8_ge_d | d | -10 mcrparted8_ge_d | e | 0 (11 rows) drop table mcrparted; -- check that wholerow vars in the RETURNING list work with partitioned tables create table returningwrtest (a int) partition by list (a); create table returningwrtest1 partition of returningwrtest for values in (1); insert into returningwrtest values (1) returning returningwrtest; returningwrtest ----------------- (1) (1 row) -- check also that the wholerow vars in RETURNING list are converted as needed alter table returningwrtest add b text; create table returningwrtest2 (b text, c int, a int); alter table returningwrtest2 drop c; alter table returningwrtest attach partition returningwrtest2 for values in (2); insert into returningwrtest values (2, 'foo') returning returningwrtest; returningwrtest ----------------- (2,foo) (1 row) drop table returningwrtest;