-- -- first, define the datatype. Turn off echoing so that expected file -- does not depend on contents of sha.sql. -- SET client_min_messages = error; \set ECHO none CREATE TABLE sha (one sha1, two sha224); CREATE INDEX sha1s_b ON sha (one); CREATE INDEX sha1s_h ON sha USING hash (two); COPY sha FROM stdin; SET enable_seqscan TO off; SELECT two FROM sha WHERE one = 'da39a3ee5e6b4b0d3255bfef95601890afd80709'; two ---------------------------------------------------------- d14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f (1 row) SELECT text(one) FROM sha WHERE two <> 'a1fcbaf07c898aa590c6f3169eef115da1a1f6521921919bee973da9'; text ------------------------------------------ da39a3ee5e6b4b0d3255bfef95601890afd80709 b262b078c9ec2797ad8980208ff773384d6bed9c (2 rows) SELECT text(one) FROM sha WHERE one >= '0000000000000000000000000000000000000000'; text ------------------------------------------ da39a3ee5e6b4b0d3255bfef95601890afd80709 d67a3c66c2ed6c8855d898bb04442ce0371c1f3b b262b078c9ec2797ad8980208ff773384d6bed9c (3 rows) CREATE TABLE md5test (val md5hash); CREATE INDEX md5_id1 ON md5test (val); CREATE INDEX md5_id2 ON md5test USING hash(val); INSERT INTO md5test VALUES (md5('test')), (md5('another test')); SELECT val FROM md5test WHERE val <> md5('another test')::md5hash; val ---------------------------------- 098f6bcd4621d373cade4e832627b4f6 (1 row) SELECT val FROM md5test WHERE val = md5('another test')::md5hash; val ---------------------------------- 5e8862cd73694287ff341e75c95e3c6a (1 row) SELECT text(val) FROM md5test WHERE val < 'ffffffffffffffffffffffffffffffff'; text ---------------------------------- 098f6bcd4621d373cade4e832627b4f6 5e8862cd73694287ff341e75c95e3c6a (2 rows) CREATE TABLE md5test_after (val md5hash); COPY md5test TO '/tmp/tst' WITH (FORMAT binary); COPY md5test_after FROM '/tmp/tst' WITH (FORMAT binary); SELECT * FROM md5test; val ---------------------------------- 098f6bcd4621d373cade4e832627b4f6 5e8862cd73694287ff341e75c95e3c6a (2 rows) SELECT * FROM md5test_after; val ---------------------------------- 098f6bcd4621d373cade4e832627b4f6 5e8862cd73694287ff341e75c95e3c6a (2 rows)