Is there a way to generate hash integer within a predefined range for a string in PostgreSQL?

Question

For sharding I need good hash algorithm. I need to generate hash integer value for string within 0-2^31 range. Is this possible ?

Following answer give a way to generate integer from md5 algorithm.

But is there a way to generate this value within a range ?

yeah, modulo operator % almost does, what you need - you just need to take account negative remainders stackoverflow.com/questions/22683600/… — pozs
– pozs, Commented Nov 12, 2014 at 11:33
People usually do this on the application side, because in general the application is doing data routing to shards. It's somewhat odd to need this at the database level. — Craig Ringer
– Craig Ringer, Commented Nov 12, 2014 at 11:36
Need to avoid modulo operator. This is to implement consistent-hashing. I'm trying to implement this as database functions so pgpool can use it. I don't have the luxury to implement it application layer ( such as java ). As design design I need to separate application from postgresql cluster information. — Viraj
– Viraj, Commented Nov 12, 2014 at 11:39
@Viraj and why do you think, modulo isn't consistent? -- btw in your special case (your range is 0-2^31) you can use bitwise operators too, to extract the lower 31 bit. — pozs
– pozs, Commented Nov 12, 2014 at 11:51

Community · Accepted Answer · 2017-05-23 11:57:55Z

1

In the function suggested in Hashing a String to a Numeric Value in PostgresSQL, the 32 bits range is expressed as the width of the bit(N) cast.

It cannot be passed as a parameter because a type modifier must be constant, but to get a fixed 31 bits, you may just adjust the function like this:

CREATE FUNCTION hash_string_into_31_bits(text) RETURNS int AS $$
   select ('x'||substr(md5($1),1,8))::bit(31)::int;
$$ language sql;

Then the result will be always 31 bits wide and positive, which is the same thing.

CommunityBot

11 silver badge

answered Nov 12, 2014 at 12:37

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1