Infrastructure at your Service

Daniel Westermann

New SHA-2 functions showing up in PostgreSQL 11

A recent commit announced new SHA-2 functions coming up in PostgreSQL 11. Until now you can use the md5 function to generate hashes for test data or whatever you want. This commit adds more functions you can use for that. Let’s see how they work.

When you want to try what follows make sure you are on the development version of PostgreSQL. You can find a little howto here.

For generating test data in PostgreSQL I often use things like that:

postgres@pgbox:/home/postgres/ [PGDEV] psql
psql (11devel)
Type "help" for help.

postgres=# \! cat a.sql
drop table if exists t1;
create table t1 as
  select a.*
       , md5(a::varchar)
    from generate_series (1,1000000) a;
postgres=# \i a.sql
psql:a.sql:1: NOTICE:  table "t1" does not exist, skipping
DROP TABLE
SELECT 1000000
postgres=# select * from t1 limit 5;
 a |               md5                
---+----------------------------------
 1 | c4ca4238a0b923820dcc509a6f75849b
 2 | c81e728d9d4c2f636f067f89cc14862c
 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
 4 | a87ff679a2f3e71d9181a67b7542122c
 5 | e4da3b7fbbce2345d7772b0674a318d5
(5 rows)

Now we have more function to chose from:

postgres=# \df *sha*
                                       List of functions
   Schema   |               Name               | Result data type | Argument data types | Type 
------------+----------------------------------+------------------+---------------------+------
 pg_catalog | pg_advisory_lock_shared          | void             | bigint              | func
 pg_catalog | pg_advisory_lock_shared          | void             | integer, integer    | func
 pg_catalog | pg_advisory_unlock_shared        | boolean          | bigint              | func
 pg_catalog | pg_advisory_unlock_shared        | boolean          | integer, integer    | func
 pg_catalog | pg_advisory_xact_lock_shared     | void             | bigint              | func
 pg_catalog | pg_advisory_xact_lock_shared     | void             | integer, integer    | func
 pg_catalog | pg_relation_is_publishable       | boolean          | regclass            | func
 pg_catalog | pg_stat_reset_shared             | void             | text                | func
 pg_catalog | pg_try_advisory_lock_shared      | boolean          | bigint              | func
 pg_catalog | pg_try_advisory_lock_shared      | boolean          | integer, integer    | func
 pg_catalog | pg_try_advisory_xact_lock_shared | boolean          | bigint              | func
 pg_catalog | pg_try_advisory_xact_lock_shared | boolean          | integer, integer    | func
 pg_catalog | sha224                           | bytea            | bytea               | func
 pg_catalog | sha256                           | bytea            | bytea               | func
 pg_catalog | sha384                           | bytea            | bytea               | func
 pg_catalog | sha512                           | bytea            | bytea               | func

Using the same test script as before but with the sha224 function:

postgres=# \! cat a.sql
drop table if exists t1;
create table t1 as
  select a.*
       , sha224(a::text::bytea)
    from generate_series (1,1000000) a;
postgres=# \i a.sql
DROP TABLE
SELECT 1000000
postgres=# select * from t1 limit 5;
 a |                           sha224                           
---+------------------------------------------------------------
 1 | \xe25388fde8290dc286a6164fa2d97e551b53498dcbf7bc378eb1f178
 2 | \x58b2aaa0bfae7acc021b3260e941117b529b2e69de878fd7d45c61a9
 3 | \x4cfc3a1811fe40afa401b25ef7fa0379f1f7c1930a04f8755d678474
 4 | \x271f93f45e9b4067327ed5c8cd30a034730aaace4382803c3e1d6c2f
 5 | \xb51d18b551043c1f145f22dbde6f8531faeaf68c54ed9dd79ce24d17
(5 rows)

You can use the other functions in the same way, of course.

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure