Postgresql 12 Custom Hash Function

Question

I have a large table that I would like to partition. The field I want to partition against is a string that contains a coded value, but appears as an integer stored as a string. The "number" is made up of 4 digits. The first two are what I want to partition against. For instance, the field value of "1298", would need to partition using the value 12. I thought this would be a great use for a hash partition with a custom hash function.

For some reference: Queries are frequently made against this column in the form of ... WHERE TheField = '0123' or ... WHERE TheField IN ('0123', '0145', '0232')

It would be impractical to manually each value to go into each partition, so I thought using a custom hash function would be a practical solution.

Actual Question: How do I make this custom hash function and use it to define the partitions?

Answer 1

You can partition by substr(thefield, 1, 2), but there is no point in doing that unless

• you delete data by that criterion

or

• your queries use that expression

You should simply partition by hash on thefield, then both your queries can use partition pruning. There is no need for a custom hash function.

Answer 2

Although this isn't a true answer to the question of creating a custom hash function, the solution to my overall problem is that PostgreSQL DOES in fact support range partitions over a text field as shown by mustaccio.

CREATE TABLE dept (id CHAR(4) PRIMARY KEY) PARTITION BY RANGE(id);
CREATE TABLE dept_00 PARTITION OF dept FOR VALUES FROM ('0000') TO ('0099');
CREATE TABLE dept_01 PARTITION OF dept FOR VALUES FROM ('0100') TO ('0199');
...