Thread: PG10 declarative partitioning that allow null value

Hello All,
currently we are planning to migrate inheritance partitioning to 
declarative partitioning by range values of int on pg10. But the key 
values can contain null. I know that range partitioning does not allow 
null values.
We are looking for workaround for this, that is create a list 
partitioning and then followed by range sub-partitioning. List 
partitioning of number of digits of the int key column.


CREATE TABLE partitioned_table_name (like table_name) partition by list 
(floor(log(int_key_column)+1));

create table partitions_1 partition of partitioned_table_name for values 
in (null);
create table partitions_2 partition of partitioned_table_name for values 
in (1);
.....
.....
create table partitions_2 partition of partitioned_table_name for values 
in (9) partition by range(int_key_column);

Question is :
is this a good practice ? will performance dropped significantly due to 
the process of counting the number of digits ?
or is there any better workaround for this ?

Thanks,

Soni.

I think it's better to had list partitioning of true/false based on 
(int_key_column is null)
CREATE TABLE partitioned_table_name (like table_name) partition by list 
((int_key_column is null));

On 16/04/2019 9:37, Soni wrote:
> Hello All,
> currently we are planning to migrate inheritance partitioning to 
> declarative partitioning by range values of int on pg10. But the key 
> values can contain null. I know that range partitioning does not allow 
> null values.
> We are looking for workaround for this, that is create a list 
> partitioning and then followed by range sub-partitioning. List 
> partitioning of number of digits of the int key column.
>
>
> CREATE TABLE partitioned_table_name (like table_name) partition by 
> list (floor(log(int_key_column)+1));
>
> create table partitions_1 partition of partitioned_table_name for 
> values in (null);
> create table partitions_2 partition of partitioned_table_name for 
> values in (1);
> .....
> .....
> create table partitions_2 partition of partitioned_table_name for 
> values in (9) partition by range(int_key_column);
>
> Question is :
> is this a good practice ? will performance dropped significantly due 
> to the process of counting the number of digits ?
> or is there any better workaround for this ?
>
> Thanks,
>
> Soni.
>

Soni <diptatapa@gmail.com> writes:
>> currently we are planning to migrate inheritance partitioning to 
>> declarative partitioning by range values of int on pg10. But the key 
>> values can contain null. I know that range partitioning does not allow 
>> null values.

In v11 you could use a default partition ...

            regards, tom lane