Re: PG11 Hash partitioning and null values in the partition key

[amul sul <sulamul@xxxxxxxxx>]

On Fri, Jul 13, 2018, 7:35 PM Daniel Westermann <daniel.westermann@xxxxxxxxxxxxxxxx> wrote:

Hi,

given this setup:

create table part2 ( a int, list varchar(10) ) partition by hash (a);

create table part2_1 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 0);

create table part2_2 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 1);

create table part2_3 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 2);

insert into part2 (a,list) values (1,'aaa');

insert into part2 (a,list) values (2,'bbb');

insert into part2 (a,list) values (3,'ccc');

... it is possible to insert rows like this which will always go to the first partition:

insert into part2 (a,list) values (null,'ddd');

insert into part2 (a,list) values (null,'eee');

select * from part2_1;

 a | list
---+------
 2 | bbb
   | ddd
   | eee
(3 rows)

I suppose this is intended but I could not find anything in the documentation about that. Can someone please clarify the logic behind that?

The calculated hash value for the null value will be zero, therefore, it will fall to the partition having remainder zero.

​Regards,

Amul​