encode(bytea_value, 'escape') in PostgreSQL 9.0

Florian Weimer <fweimer@bfk.de> writes:

The old 'escape' encoding used by PostgreSQL 8.4 and prior was pretty
helpful for getting human-readable strings in psql. It seems this
functionality was removed in PostgreSQL 9.0. Was this an accident or
a deliberate decision? Could we get it back, please?

I think you're looking for "set bytea_output = escape".

regards, tom lane

* Tom Lane:

Florian Weimer <fweimer@bfk.de> writes:

The old 'escape' encoding used by PostgreSQL 8.4 and prior was pretty
helpful for getting human-readable strings in psql. It seems this
functionality was removed in PostgreSQL 9.0. Was this an accident or
a deliberate decision? Could we get it back, please?

I think you're looking for "set bytea_output = escape".

To me, this seems problematic as a general recommendation because
programs won't use this, and it's confusing to have different output
in psql than what your program sees. That's why I don't want to put
it into .psqlrc. The separate command will raise a few eyebrows here
and there. 8-/

Put differently, I think it's rather odd that in 9.0, both
encode(bytea_value, 'escape') and encode(bytea_value, 'hex') output
hexadecimal values. There's also an explicit way to request such
output, so I don't think that encode() should obey the bytea_output
setting. In 8.4's psql, a BYTEA column and its escape-encoded TEXT
were displayed differently, so there is precedent.

--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

Florian Weimer <fweimer@bfk.de> writes:

Put differently, I think it's rather odd that in 9.0, both
encode(bytea_value, 'escape') and encode(bytea_value, 'hex') output
hexadecimal values.

I don't believe that; encode produces text not bytea, so its result
is not affected by this setting.

regards, tom lane

* Tom Lane:

Florian Weimer <fweimer@bfk.de> writes:

Put differently, I think it's rather odd that in 9.0, both
encode(bytea_value, 'escape') and encode(bytea_value, 'hex') output
hexadecimal values.

I don't believe that; encode produces text not bytea, so its result
is not affected by this setting.

And you are right, as usual. It turns out that we've got a
double-encoding issue in the loader. How embarrassing.

--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

On 2010-12-06, Florian Weimer <fweimer@bfk.de> wrote:

* Tom Lane:

Florian Weimer <fweimer@bfk.de> writes:

The old 'escape' encoding used by PostgreSQL 8.4 and prior was pretty
helpful for getting human-readable strings in psql. It seems this
functionality was removed in PostgreSQL 9.0. Was this an accident or
a deliberate decision? Could we get it back, please?

I think you're looking for "set bytea_output = escape".

To me, this seems problematic as a general recommendation because
programs won't use this,

programs should be using libpq's unescape_bytea and thus be
immune to changes in the representation.

If you want predictable representation base64 (or hex) your data and
store it in a text column postgres will compress long values so not
hardly any space will be wasted.

and it's confusing to have different output
in psql than what your program sees. That's why I don't want to put
it into .psqlrc. The separate command will raise a few eyebrows here
and there. 8-/

ALTER DATABASE whatever SET bytea_output = escape;

or

ALTER USER whoever SET bytea_output = escape;

--
⚂⚃ 100% natural