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