Don't allow logging in with empty password.

[postgresql] / src / test / regress / expected / insert.out

--
-- 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" 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;
--
-- 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;
drop type insert_test_type;
-- direct partition inserts should check partition bound constraint
create table range_parted (
        a text,
        b int
) partition by range (a, (b+0));
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);
-- 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).
-- 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).
-- ok
insert into part_ee_ff1 values ('ff', 1);
insert into part_ee_ff2 values ('ff', 11);
-- 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).
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
(6 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_null   |    |  0
 part_null   |    |  1
 part_ee_ff1 | ff |  1
 part_ee_ff1 | EE |  1
 part_ee_ff2 | ff | 11
 part_ee_ff2 | EE | 10
(8 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)

-- cleanup
drop table range_parted, 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;
-- 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 someone_else;
grant select (a) on key_desc_1 to someone_else;
grant insert on key_desc to someone_else;
set role someone_else;
-- 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 someone_else;
set role someone_else;
-- 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 someone_else;
revoke all on key_desc_1 from someone_else;
drop role someone_else;
drop table key_desc, key_desc_1;
-- check multi-column range partitioning expression enforces the same
-- constraint as what tuple-routing would determine it to be
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, 0);
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, 0);
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, 0) to (30, 20, maxvalue);
create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (maxvalue, 0, 0);
-- 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 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, 0) 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, 0);
\d+ mcrparted
                                 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, 0) 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, 0)

\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, 0) 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, 0)
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;