Silly little question, but is there something to input/output hex
escaped data into a bytea, ala

CREATE TABLE a (lob BYTEA);
INSERT into a (lob) VALUES ('\x01\x02\x00\x03\x04');
INSERT into a (lob) VALUES ('\x01\x00\x02\x00\x03\x04');

It seems to work (doesn't error), but when selecting the data back out,
it is truncated at the first \x00, ala

SELECT lob FROM a;

lob
----------
\001\002
\001

SELECT octet_length(lob) from a;

octet_length
--------------
2
1

Why does this happen? Also, can I get the output in similar hex form?

"Michael Artz" <mlartz@gmail.com> writes:
> Silly little question, but is there something to input/output hex
> escaped data into a bytea, ala

PQescapeBytea, perhaps? The way you are doing it has multiple problems.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Eh, I'll just convert it all to octal, I just thought that I could get away
without any middleman.

What ig going on behind the scenes? Does it first get converted to text and
then on to bytea? Would an explicit cast get around this (perhaps with
associated UDF)?

-Mike

On 5/16/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Michael Artz" <mlartz@gmail.com> writes:
> > Silly little question, but is there something to input/output hex
> > escaped data into a bytea, ala
>
> PQescapeBytea, perhaps? The way you are doing it has multiple problems.
>
> regards, tom lane
>

"Michael Artz" <mlartz@gmail.com> writes:
> What ig going on behind the scenes? Does it first get converted to text and
> then on to bytea?

No, the \nnn escape is built into the lexer's syntax for a string
literal (see backend/parser/scan.l), and only after that does the string
get fed to bytea's input routine (or any other datatype's either).
In hindsight this was a horribly bad idea that we'll be paying through
the nose for, for some time to come :-(. But we're stuck with it for
the moment.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane wrote:
> "Michael Artz" <mlartz@gmail.com> writes:
> > What ig going on behind the scenes? Does it first get converted to text and
> > then on to bytea?
>
> No, the \nnn escape is built into the lexer's syntax for a string
> literal (see backend/parser/scan.l), and only after that does the string
> get fed to bytea's input routine (or any other datatype's either).
> In hindsight this was a horribly bad idea that we'll be paying through
> the nose for, for some time to come :-(. But we're stuck with it for
> the moment.

Should we rethink this for SQL standard strings?

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> No, the \nnn escape is built into the lexer's syntax for a string
>> literal (see backend/parser/scan.l),

> Should we rethink this for SQL standard strings?

We already have: \ isn't an escape anymore when
standard_conforming_strings is true.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly