Q:

Storing SHA1 hex value in PostgreSQL

Seemingly simple question, corresponds to another question that was asked with regards to MySQL: How does one store the hex value that results from a SHA1 hash in a PostgreSQL database?
Note: I realize I could use a VARCHAR(40) field, but this isn't efficient, as the data is in hex. Also, I am using PHP to interact with the database, so I can use PHP functions if necessary, but if this is the case, what do I store the result as in the database?

A:

I would store as bytea, hex encoded.  Converting the human-readable hex data to bytea is simply a matter of:
  ('\x' || sha1_hex_value)::bytea

The only real disadvantage here is that depending on your app framework you may get a binary representation out.  If not you will get an escaped version  and depending on the escape settings, may want to convert to binary yourself (if it is hex though you can just strip off the \x at the front of the value and use as hex).

