Thread: bytea hex input/output
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
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
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
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. +
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