postgresql - How to know which partition will be used in Postgres hash partitioning?

Question

Is there a way to know which partition will be used for a new row in a table partitioned by a column with text values?

Use case is: I have a big table with many partitions according to a text column. I need to insert-update millions of new rows to this table. If I know which partitions will be used for each row, I can rearrange my bulk inserts-updates to only work on 1 partition at a time.

-> hashtext function is tested and it is not giving the correct partition value.

Answer 1

Reverse engineering the code, you can get the partition number with the following statement:

SELECT (hashtextextended('value', 8816678312871386365)::numeric + 5305509591434766563) % 8;

Replace 8 with the number of partitions and 'value' with the string in question.

You can test the partition number with satisfies_hash_partition. To test if 'value' would end up in partition 6 of 8 in table tab, you can run

SELECT satisfies_hash_partition('tab'::regclass, 8, 6, 'value'::text);