How to set bytea_output to hex?

Question

I cannot seem to change bytea_output to hex.

I have tried this method:

ALTER DATABASE dbname SET bytea_output TO 'hex';

I have tried to edit postgresql.conf

bytea_output = 'hex'

and restarted the server.

I have tried

SET bytea_output = 'hex'

and it works per transaction, but that is very tedious.

However, whenever I browse my bytea columns in phpPgAdmin, the columns are outputted escaped.

This

SELECT setting FROM pg_settings WHERE name = 'bytea_output'

returns escape.

How can bytea_output be set permanently to hex?

Answer 1

Sounds like PhpPgAdmin is probably doing a session-level SET bytea_output = 'escape'.

If you open a new psql session and SHOW bytea_output; it'll be hex there.

PhpPgAdmin may offer a way to control this behaviour, or it might be hard-coded to work around compatibility issues (if it doesn't understand the hex format for bytea).