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.