Hashing a String to a Numeric Value in PostgreSQL

by

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;

More Related Contents:

  1. What are ‘$$’ used for in PL/pgSQL
  2. INSERT with dynamic table name in trigger function
  3. The forgotten assignment operator “=” and the commonplace “:=”
  4. Truncating all tables in a Postgres database
  5. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  6. Generating a UUID in Postgres for Insert statement?
  7. How to set value of composite variable field using dynamic SQL
  8. How do I do large non-blocking updates in PostgreSQL?
  9. Execute a dynamic crosstab query
  10. How to add “on delete cascade” constraints?
  11. Is there a way to disable function overloading in Postgres
  12. Can I make a plpgsql function return an integer without using a variable?
  13. Dynamic SQL (EXECUTE) as condition for IF statement
  14. Returning from a function with OUT parameter
  15. SELECT raises exception in PL/pgSQL function
  16. Return SETOF rows from PostgreSQL function
  17. Error when setting n_distinct using a plpgsql variable
  18. Postgres FOR LOOP
  19. Dynamically generate columns for crosstab in PostgreSQL
  20. Function to loop through and select data from multiple tables
  21. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  22. SQL Sub queries in check constraint
  23. Cursor based records in PostgreSQL
  24. Iterating over integer[] in PL/pgSQL
  25. ERROR: unterminated quoted string at or near
  26. How can I send some http request from postgresql function or trigger
  27. Select from a table variable
  28. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  29. Loop through columns of RECORD
  30. Format specifier for integer variables in format() for EXECUTE?

Leave a Comment