Re: Re: Re: Re: binary data

On Wed, 27 Jun 2001, Hugh Mandeville wrote:

> how binary data is stored, escaped and unescaped seems to vary slightly
> between the text and bytea datatypes.
> 
> are the following observations correct?
> 
> 1. Escaping the special characters in the binary data
> bytea: the bytea data type needs the backslash character '\' escaped to
> '\\\\'
Correct. 

> text: the text data type needs '\' escaped to '\\'.
Correct.

> bytea and text handle all escaping all other special characters the same.
text cannot handle null character, for bytea you can use it, with '\\000'


>   test=# insert into bintest (col_varchar) values ('\\');
>   INSERT 69443 1
>   test=# insert into bintest (col_bytea) values ('\\');
>   ERROR:  Bad input string for type bytea
>   test=# insert into bintest (col_bytea) values ('\\\\');
> 
> 2. How the data is actually stored in the database.
> bytea: stores the data as binary
> text: stores all characters as binary expect 0 which it stores as \000
No, text does not and cannot store a null byte. What you see as \000 is
actually a backslash followed by three zeroes, hence you see 12 tol/tcl.

> test=# SELECT octet_length(col_bytea) AS col, col_bytea,
> octet_length(col_text) AS tol, char_length(col_text) AS tcl, col_text FROM
> bintest WHERE oid = 69458;
>  col |       col_bytea       | tol | tcl |   col_text
> -----+-----------------------+-----+-----+--------------
>    9 | \000\001\002\003hello |  12 |  12 | \000^A^B^Chello
> 
> 3. Unescaping the special characters
> bytea: PQgetvalue() returns a string with all the special characters escaped
> out.
> text: PQgetvalue returns a string with only the 0 character escaped out.
> 
>   PQgetlength() on bytea column returns 21
>   PQgetvalue() returns
>   00000000: 5c30 3030 5c30 3031 5c30 3032 5c30 3033  \000\001\002\003
>   00000010: 6865 6c6c 6f                             hello
Correct
> 
>   PQgetlength() on char column returns 12
>   PQgetvlaue() on char column returns
>   00000000: 5c30 3030 0102 0368 656c 6c6f            \000...hello
Incorrect because you didn't insert a null character there, it never
escapes anything. You have a backslash followed by zeros.


> are there any functions for escaping and unescaping binary data?
If you are using Perl DBD::Pg, its done for you automatically.

If you are using libpq, currently there are none.

-alex