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

Answer 2

Starting from PostgreSQL 11 you could use built-in functions to calculate hash value:

SELECT sha256('hello world!');
-- x7509e5bda0c762d2bac7f90d758b5b2263fa01ccbc542ab5e3df163be08e6ca9

db<>fiddle demo

Other Binary String Functions

+----------------+--------------+---------------+
|   Function     | Return Type  |  Description  |
+----------------+--------------+---------------+
| sha224(bytea)  | bytea        | SHA-224 hash  |
| sha256(bytea)  | bytea        | SHA-256 hash  |
| sha384(bytea)  | bytea        | SHA-384 hash  |
| sha512(bytea)  | bytea        | SHA-512 hash  |
+----------------+--------------+---------------+

Note that for historic reasons, the function md5 returns a hex-encoded value of type text whereas the SHA-2 functions return type bytea. Use the functions encode and decode to convert between the two, for example encode(sha256('abc'), 'hex') to get a hex-encoded text representation.

Answer 3

Starting with PostgreSQL 12 you can use a generated column; it could be as simple as:

CREATE TABLE codes (
    ...,
    key_code text,
    sha_code text GENERATED ALWAYS AS (encode(sha256(key_code::bytea), 'hex')) STORED
);

As with other methods, you may need to CREATE EXTENSION IF NOT EXISTS pgcrypto; if not already part of your schema.

Generated columns can be selected and indexed like any other:

CREATE INDEX idx_sha_codes ON codes USING btree (sha_code);
SELECT id, sha_code FROM codes WHERE sha_code = '...';

There are notable constraints e.g. they can't reference other generated columns or other tables. However, this sort of derived value is almost a perfect use case. More at https://www.postgresql.org/docs/12/ddl-generated-columns.html.