Hashing a String to a Numeric Value in PostgreSQL

postgresql plpgsql postgresql-8.4
Salman A. Kagzi picture Salman A. Kagzi · Mar 21, 2012 · Viewed 37.7k times · Source

I need to Convert Strings stored in my Database to a Numeric value. Result can be Integer (preferred) or Bigint. This conversion is to be done at Database side in a PL/pgSQL function.

Can someone please point me to some algorithm or any API's that can be used to achieve this?

I have been searching for this on Google for hours now, could not find anything useful so far :(

Answer

Daniel Vérité picture Daniel Vérité · Mar 21, 2012

Just keep the first 32 bits or 64 bits of the MD5 hash. Of course, it voids the main property of md5 (=the probability of collision being infinitesimal) but you'll still get a wide dispersion of values which presumably is good enough for your problem.

SQL functions derived from the other answers:

For bigint:

create function h_bigint(text) returns bigint as $$
 select ('x'||substr(md5($1),1,16))::bit(64)::bigint;
$$ language sql;

For int:

create function h_int(text) returns int as $$
 select ('x'||substr(md5($1),1,8))::bit(32)::int;
$$ language sql;

Related questions

Iterating over integer[] in PL/pgSQL

I am trying to loop through an integer array (integer[]) in a plpgsql function. Something like this: declare a integer[] = array[1,2,3]; i bigint; begin for i in a loop raise notice "% ",i; end loop; return true; end In my actual …

Read More
Incrementing a number in a loop in plpgsql

I couldn't find this immediately from the examples. I want to increment a variable in a loop, in a function. For instance: DECLARE iterator float4; BEGIN iterator = 1; while iterator < 999 ..... iterator ++; END; How would this be done? I was looking …

Read More
Store query result in a variable using in PL/pgSQL

How to assign the result of a query to a variable in PL/pgSQL, the procedural language of PostgreSQL? I have a function: CREATE OR REPLACE FUNCTION test(x numeric) RETURNS character varying AS $BODY$ DECLARE name character varying(255); begin …

Read More