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.
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);