Searching encrypted field in Postgres
If you look at PostgreSQL Documentation (Appendix F.25. pgcrypto - F.25.3. PGP Encryption Functions):
The given password is hashed using a String2Key (S2K) algorithm. This is rather similar to crypt() algorithms — purposefully slow and with random salt — but it produces a full-length binary key.
(Emphasis mine.)
So the following gives different results every time you run it:
select pgp_sym_encrypt('test', 'password');When testing the password use pgp_sym_decrypt instead, it can be tested like this:
select pgp_sym_decrypt(pgp_sym_encrypt('test', 'password'), 'password');