Thread: encode(bytea_value, 'escape') in PostgreSQL 9.0

encode(bytea_value, 'escape') in PostgreSQL 9.0

From
Florian Weimer
Date:
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?

--
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

Re: encode(bytea_value, 'escape') in PostgreSQL 9.0

From
Tom Lane
Date:
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

Re: encode(bytea_value, 'escape') in PostgreSQL 9.0

From
Florian Weimer
Date:
* 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

Re: encode(bytea_value, 'escape') in PostgreSQL 9.0

From
Tom Lane
Date:
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

Re: encode(bytea_value, 'escape') in PostgreSQL 9.0

From
Florian Weimer
Date:
* 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

Re: encode(bytea_value, 'escape') in PostgreSQL 9.0

From
Jasen Betts
Date:
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