This is the second follow up which covers this question: When you hash partition a table in PostgreSQL 11 where do null values for the partitioned column go to? Lets go…<\/p>\n
<\/p>\n
In the demo I used this little table:<\/p>\n
\npostgres=# select version();\n version \n-----------------------------------------------------------------------------------------------------------------------------\n PostgreSQL 11beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bi\n(1 row)\npostgres=# create table part2 ( a int, list varchar(10) ) partition by hash (a);\nCREATE TABLE\npostgres=# create table part2_1 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 0);\nCREATE TABLE\npostgres=# create table part2_2 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 1);\nCREATE TABLE\npostgres=# create table part2_3 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 2);\nCREATE TABLE\npostgres=# d+ part2\n Table \"public.part2\"\n Column | Type | Collation | Nullable | Default | Storage | Stats target | Description \n--------+-----------------------+-----------+----------+---------+----------+--------------+-------------\n a | integer | | | | plain | | \n list | character varying(10) | | | | extended | | \nPartition key: HASH (a)\nPartitions: part2_1 FOR VALUES WITH (modulus 3, remainder 0),\n part2_2 FOR VALUES WITH (modulus 3, remainder 1),\n part2_3 FOR VALUES WITH (modulus 3, remainder 2)\n<\/pre>\nThe data we played with was this:<\/p>\n
\npostgres=# insert into part2 (a,list) values (1,'beer');\nINSERT 0 1\npostgres=# insert into part2 (a,list) values (2,'whine');\nINSERT 0 1\npostgres=# insert into part2 (a,list) values (3,'schnaps');\nINSERT 0 1\npostgres=# select * from only part2_1;\n a | list \n---+-------\n 2 | whine\n(1 row)\n\npostgres=# select * from only part2_2;\n a | list \n---+---------\n 3 | schnaps\n(1 row)\n\npostgres=# select * from only part2_3;\n a | list \n---+------\n 1 | beer\n(1 row)\n<\/pre>\nWe have the data evenly distributed over the three partitions. When we insert a row which contains a NULL value for the column we partitioned on:<\/p>\n
\npostgres=# insert into part2 (a,list) values (null,'cocktail');\nINSERT 0 1\n<\/pre>\n… where does that column go to?<\/p>\n
\npostgres=# select * from only part2_1;\n a | list \n---+----------\n 2 | whine\n | cocktail\n(2 rows)\n\npostgres=# select * from only part2_2;\n a | list \n---+---------\n 3 | schnaps\n(1 row)\n\npostgres=# select * from only part2_3;\n a | list \n---+------\n 1 | beer\n(1 row)\n<\/pre>\nIt goes to the first partition and every following NULL key row gets there as well:<\/p>\n
\npostgres=# insert into part2 (a,list) values (null,'rum');\nINSERT 0 1\npostgres=# select * from only part2_1;\n a | list \n---+----------\n 2 | whine\n | cocktail\n | rum\n(3 rows)\n<\/pre>\n