Postgres: How to store bytea as value in hstore?

Question

I am trying to store a bytea as a value in hstore and I keep getting the following error:

function hstore(unknown, bytea) does not exist

Here is what I have tried:

UPDATE users set store = store || hstore('key1', pgp_pub_encrypt('testval',dearmor('xxxx')));

Answer 1

am trying to store a bytea as a value in hstore

You cannot safely store bytea in hstore without some kind of encoding, because hstore is stored as text in the current text encoding, but bytea has no text encoding and may also contain null bytes that are not legal in text strings.

So you need to encode the bytea as hex, base64, or some other form that makes it into valid text in the current encoding.

The simplest way is to cast it to text, but I recommend instead using encode and decode to/from base64. This encoding is more compact than the text or hex encodings used for bytea's text representation in PostgreSQL, and it's also independent of the value of the bytea_output setting.

e.g.

UPDATE users
SET store = store
    || hstore('key1', encode( pgp_pub_encrypt('testval',dearmor('xxxx'))), 'base64')
WHERE ... ;

Answer 2

There are two variants of the hstore() function taking arrays, one taking a row / record and one taking two text values. The last one is for you:

SELECT hstore('foo','abc'::bytea::text);  -- cast bytea to text!

Your attempt failed the rules of function type resolution because there is no explicit cast registered from bytea to text. Postgres falls back to input / output conversion for that, which does not count for function type resolution:

• Is there a way to disable function overloading in Postgres

So:

UPDATE users
SET    store = store
        || hstore('key1', pgp_pub_encrypt('testval',dearmor('xxxx'))::text)
WHERE ... ;