Hash a column in postgres using sha-256

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();