0

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?

3
  • It's not exactly clear what you're asking here. Also, what you describe looks like range partitioning to me.
    – mustaccio
    Sep 14, 2021 at 18:19
  • Can I use a range partition on a string though? I don't believe you can. Sep 14, 2021 at 19:30
  • You don't have to "believe"; you can always try and see.
    – mustaccio
    Sep 14, 2021 at 20:41

2 Answers 2

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.