Hash a column in postgres using sha-256

Question

I have a postrges database with a table contains key codes that I have generated using a python function. I would like to be able to hash this column such that each time a key code is added to it, the key is hashed. How can I get postgres to do that? Or what will be the best way to store these codes. Here is a sample of the column I would like to hash in my table.

 key_codes |  -----------+  L7G4J83K  |          J70KG169  |           L69E540K  |          GL8E9C3J  |           6C0LE215  |           9G01C8JA  |           1G9KC58A  |          

Answer 1

Use a trigger to set the hash column on insert and update. For SHA-256, use the pgcrypto extension module's digest function.

Since you haven't specified your PostgreSQL version I'll assume you're using the current 9.2 in the following examples.

Here's how to invoke a sha256 digest function:

regress=# CREATE EXTENSION pgcrypto; CREATE EXTENSION  regress=> SELECT digest('blah', 'sha256');                                digest                                --------------------------------------------------------------------  \x8b7df143d91c716ecfa5fc1730022f6b421b05cedee8fd52b1fc65a96030ad52 (1 row) 

Note that the CREATE EXTENSION function must be run as a superuser.

The trigger is pretty simple. Something like this would do, assuming your table looks like this:

CREATE TABLE some_table ( key_codes text, hash bytea );  CREATE OR REPLACE FUNCTION hash_update_tg() RETURNS trigger AS $$ BEGIN     IF tg_op = 'INSERT' OR tg_op = 'UPDATE' THEN         NEW.hash = digest(NEW.key_codes, 'sha256');         RETURN NEW;     END IF; END; $$ LANGUAGE plpgsql;  CREATE TRIGGER some_table_hash_update  BEFORE INSERT OR UPDATE ON some_table  FOR EACH ROW EXECUTE PROCEDURE hash_update_tg(); 

Usage:

regress=> INSERT INTO some_table(key_codes) VALUES ('fred'); INSERT 0 1 regress=> SELECT * FROM some_table;  key_codes |                                hash                                 -----------+--------------------------------------------------------------------  fred      | \xd0cfc2e5319b82cdc71a33873e826c93d7ee11363f8ac91c4fa3a2cfcd2286e5 (1 row) 

You can reduce the overhead of the trigger execution by making the update trigger conditional. Instead of the above CREATE TRIGGER, use both of these:

CREATE TRIGGER some_table_hash_insert BEFORE INSERT ON some_table  FOR EACH ROW  EXECUTE PROCEDURE hash_update_tg();   CREATE TRIGGER some_table_hash_update  BEFORE UPDATE ON some_table  FOR EACH ROW  WHEN ( NEW.key_codes IS DISTINCT FROM OLD.key_codes )  EXECUTE PROCEDURE hash_update_tg();