begin; \set ECHO none create schema part_test; 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 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"); 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) 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 >= 'C' part_test.bar | part_test.bar_p1 | (,A) | str < 'A' part_test.bar | part_test.bar_p2 | [A,C) | str >= 'A' and str < '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) rollback;