\set ECHO queries
create extension range_partitioning;
create schema part_test;
select value_in_range('5','[1,10]','int4range');
 value_in_range 
----------------
 t
(1 row)

select value_in_range('11','[1,10]','int4range');
 value_in_range 
----------------
 f
(1 row)

select is_subrange('[4,5]','[1,10)','int4range');
 is_subrange 
-------------
 t
(1 row)

select is_subrange('[4,5]','[7,10)','int4range');
 is_subrange 
-------------
 f
(1 row)

select is_subrange('[4,7]','[5,10)','int4range');
 is_subrange 
-------------
 f
(1 row)

select range_add('[4,5]','(5,10]','int4range');
 range_add 
-----------
 [4,11)
(1 row)

select range_add('[4,5]','[5,10]','int4range');
 range_add 
-----------
 [4,11)
(1 row)

select range_add('[4,5]','[7,10]','int4range');
ERROR:  result of range union would not be contiguous
CONTEXT:  SQL statement "select $1::int4range + $2::int4range"
PL/pgSQL function range_add(text,text,text) line 5 at EXECUTE
select range_subtract('[1,10]','(5,10]','int4range');
 range_subtract 
----------------
 [1,6)
(1 row)

select range_subtract('[1,10]','[1,5]','int4range');
 range_subtract 
----------------
 [6,11)
(1 row)

select range_subtract('[1,5]','[3,10]','int4range');
 range_subtract 
----------------
 [1,3)
(1 row)

select constructor_clause('1','5','[]','int4range');
   constructor_clause    
-------------------------
 int4range('1','5','[]')
(1 row)

select  where_clause('x','(,)','int4range') as w1,
        where_clause('d','(,2015-01-01)','daterange') as w2,
        where_clause('y','[4,5]','int4range') as w3,
        where_clause('z','[4,5)','int4range') as w4,
        where_clause('z','empty','int4range') as w5;
  w1  |        w2        |          w3          |          w4          |  w5   
------+------------------+----------------------+----------------------+-------
 true | d < '01-01-2015' | y >= '4' and y < '6' | z >= '4' and z < '5' | false
(1 row)

create view p_info
as
select  c.relname, p.partition_number, p.range
from    partition p
join    pg_class c
on      c.oid = p.partition_class;
create table part_test.foo( x integer );
select create_parent('part_test.foo','x');
 create_parent 
---------------
 
(1 row)

select * from p_info order by 1,2;
 relname | partition_number | range 
---------+------------------+-------
 foo_p0  |                0 | (,)
(1 row)

select  mt.relname as table_name,
        pt.relname as partition_name,
        cons.consrc
from    pg_namespace vs
join    pg_class mt
on      vs.oid = mt.relnamespace
        and
        mt.relkind = 'r'
join    pg_inherits i
on      i.inhparent = mt.oid
join    pg_class pt
on      pt.oid = i.inhrelid
        and
        pt.relkind = 'r'
join    pg_constraint cons
on      cons.conrelid = pt.oid
        and
        cons.contype = 'c'
where   vs.nspname = 'part_test'
and     mt.relname = 'foo'
order by 1,2,3;
 table_name | partition_name | consrc 
------------+----------------+--------
 foo        | foo_p0         | true
(1 row)

select create_partition('part_test.foo','[5000,)');
 create_partition 
------------------
 
(1 row)

select * from p_info order by 1,2;
 relname | partition_number |  range  
---------+------------------+---------
 foo_p0  |                0 | (,5000)
 foo_p1  |                1 | [5000,)
(2 rows)

select exists( select null from pg_class where relname = 'foo_p0');
 exists 
--------
 t
(1 row)

select exists( select null from pg_class where relname = 'foo_p1');
 exists 
--------
 t
(1 row)

insert into part_test.foo(x)
values (10),(4999),(5000),(15000);
select * from part_test.foo_p0 order by 1;
  x   
------
   10
 4999
(2 rows)

select * from part_test.foo_p1 order by 1;
   x   
-------
  5000
 15000
(2 rows)

select create_partition('part_test.foo','[10000,)');
 create_partition 
------------------
 
(1 row)

select * from p_info order by 1,2;
 relname | partition_number |    range     
---------+------------------+--------------
 foo_p0  |                0 | (,5000)
 foo_p1  |                1 | [5000,10000)
 foo_p2  |                2 | [10000,)
(3 rows)

select * from part_test.foo_p0 order by 1;
  x   
------
   10
 4999
(2 rows)

select * from part_test.foo_p1 order by 1;
  x   
------
 5000
(1 row)

select * from part_test.foo_p2 order by 1;
   x   
-------
 15000
(1 row)

select get_destination_partition('part_test.foo','4998');
 get_destination_partition 
---------------------------
 part_test.foo_p0
(1 row)

select get_destination_partition('part_test.foo','5000');
 get_destination_partition 
---------------------------
 part_test.foo_p1
(1 row)

select master_class::regclass::text as m, partition_class::regclass::text as p, range, where_clause(partition_class) as sql
from partition
order by 1,2;
       m       |        p         |    range     |             sql             
---------------+------------------+--------------+-----------------------------
 part_test.foo | part_test.foo_p0 | (,5000)      | x < '5000'
 part_test.foo | part_test.foo_p1 | [5000,10000) | x >= '5000' and x < '10000'
 part_test.foo | part_test.foo_p2 | [10000,)     | x >= '10000'
(3 rows)

select drop_partition('part_test.foo_p1','part_test.foo_p0');
 drop_partition 
----------------
 
(1 row)

select * from p_info order by 1,2;
 relname | partition_number |  range   
---------+------------------+----------
 foo_p0  |                0 | (,10000)
 foo_p2  |                2 | [10000,)
(2 rows)

select * from part_test.foo_p0 order by 1;
  x   
------
   10
 4999
 5000
(3 rows)

select * from part_test.foo_p2 order by 1;
   x   
-------
 15000
(1 row)

select drop_partition('part_test.foo_p0','part_test.foo_p2');
 drop_partition 
----------------
 
(1 row)

select * from p_info order by 1,2;
 relname | partition_number | range 
---------+------------------+-------
 foo_p2  |                2 | (,)
(1 row)

select * from part_test.foo_p2 order by 1;
   x   
-------
    10
  4999
  5000
 15000
(4 rows)

create type range_partitioning_textrange_c as range (subtype = text, collation = "C");
select value_in_range('abc','[a,c]','range_partitioning_textrange_c');
 value_in_range 
----------------
 t
(1 row)

select value_in_range('efg','[a,c]','range_partitioning_textrange_c');
 value_in_range 
----------------
 f
(1 row)

select is_subrange('[abc,def]','[a,e)','range_partitioning_textrange_c');
 is_subrange 
-------------
 t
(1 row)

select is_subrange('[abc,xyz]','[a,e)','range_partitioning_textrange_c');
 is_subrange 
-------------
 f
(1 row)

select is_subrange('[abc,def]','[b,z)','range_partitioning_textrange_c');
 is_subrange 
-------------
 f
(1 row)

select range_add('[abc,def]','(def,xyz]','range_partitioning_textrange_c');
 range_add 
-----------
 [abc,xyz]
(1 row)

select range_add('[abc,def]','[def,xyz]','range_partitioning_textrange_c');
 range_add 
-----------
 [abc,xyz]
(1 row)

select range_add('[abc,def]','[ijk,xyz]','range_partitioning_textrange_c');
ERROR:  result of range union would not be contiguous
CONTEXT:  SQL statement "select $1::range_partitioning_textrange_c + $2::range_partitioning_textrange_c"
PL/pgSQL function range_add(text,text,text) line 5 at EXECUTE
select range_subtract('[abc,xyz]','[abc,def]','range_partitioning_textrange_c');
 range_subtract 
----------------
 (def,xyz]
(1 row)

select range_subtract('[abc,xyz]','[ijk,xyz]','range_partitioning_textrange_c');
 range_subtract 
----------------
 [abc,ijk)
(1 row)

select range_subtract('[def,deg]','[abc,xyz]','range_partitioning_textrange_c');
 range_subtract 
----------------
 empty
(1 row)

select constructor_clause('ab,c','def','[]','range_partitioning_textrange_c');
                constructor_clause                 
---------------------------------------------------
 range_partitioning_textrange_c('ab,c','def','[]')
(1 row)

create table part_test.bar(str text collate "C");
select exists( select null from pg_attribute where attrelid = 'part_test.bar'::regclass and attname = 'str' );
 exists 
--------
 t
(1 row)

select create_parent('part_test.bar','str');
 create_parent 
---------------
 
(1 row)

select * from p_info order by 1,2;
 relname | partition_number | range 
---------+------------------+-------
 bar_p0  |                0 | (,)
 foo_p2  |                2 | (,)
(2 rows)

select create_partition('part_test.bar','(,A)');
 create_partition 
------------------
 
(1 row)

select * from p_info order by 1,2;
 relname | partition_number | range 
---------+------------------+-------
 bar_p0  |                0 | [A,)
 bar_p1  |                1 | (,A)
 foo_p2  |                2 | (,)
(3 rows)

select create_partition('part_test.bar','[A,C)');
 create_partition 
------------------
 
(1 row)

select * from p_info order by 1,2;
 relname | partition_number | range 
---------+------------------+-------
 bar_p0  |                0 | [C,)
 bar_p1  |                1 | (,A)
 bar_p2  |                2 | [A,C)
 foo_p2  |                2 | (,)
(4 rows)

select get_destination_partition('part_test.bar','ABEL');
 get_destination_partition 
---------------------------
 part_test.bar_p2
(1 row)

select get_destination_partition('part_test.bar','CHARLIE');
 get_destination_partition 
---------------------------
 part_test.bar_p0
(1 row)

insert into part_test.bar
values ('ABEL'),('BAKER'),('CHARLIE');
select * from part_test.bar_p0 order by 1;
   str   
---------
 CHARLIE
(1 row)

select * from part_test.bar_p1 order by 1;
 str 
-----
(0 rows)

select * from part_test.bar_p2 order by 1;
  str  
-------
 ABEL
 BAKER
(2 rows)

select master_class::regclass::text as m, partition_class::regclass::text as p, range, where_clause(partition_class) as sql
from partition
order by 1,2;
       m       |        p         | range |                       sql                        
---------------+------------------+-------+--------------------------------------------------
 part_test.bar | part_test.bar_p0 | [C,)  | str COLLATE "C" >= 'C'
 part_test.bar | part_test.bar_p1 | (,A)  | str COLLATE "C" < 'A'
 part_test.bar | part_test.bar_p2 | [A,C) | str COLLATE "C" >= 'A' and str COLLATE "C" < 'C'
 part_test.foo | part_test.foo_p2 | (,)   | true
(4 rows)

select drop_partition('part_test.bar_p1','part_test.bar_p2');
 drop_partition 
----------------
 
(1 row)

select * from p_info order by 1,2;
 relname | partition_number | range 
---------+------------------+-------
 bar_p0  |                0 | [C,)
 bar_p2  |                2 | (,C)
 foo_p2  |                2 | (,)
(3 rows)

select * from part_test.bar_p0 order by 1;
   str   
---------
 CHARLIE
(1 row)

select * from part_test.bar_p2 order by 1;
  str  
-------
 ABEL
 BAKER
(2 rows)

set search_path = public;
select partition_class::regclass::text, refresh_exclusion_constraint(partition_class) from partition order by 1;
 partition_class  | refresh_exclusion_constraint 
------------------+------------------------------
 part_test.bar_p0 | t
 part_test.bar_p2 | t
 part_test.foo_p2 | t
(3 rows)

create type duplicate_int_range as range (subtype = integer );
create table dupe_test( x integer);
select create_parent('dupe_test','x');
ERROR:  Multiple range types (int4range, duplicate_int_range) are valid for column x on dupe_test
HINT:  Specify one of those types in the p_qual_range_type parameter
select create_parent('dupe_test','x', p_qual_range_type := 'int4range');
 create_parent 
---------------
 
(1 row)

create table dupe_test2( x integer);
select create_parent('dupe_test2','x', p_qual_range_type := 'duplicate_int_range');
 create_parent 
---------------
 
(1 row)