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
'\\\\'
text: the text data type needs '\' escaped to '\\'.
bytea and text handle all escaping all other special characters the same.
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
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\00300000010: 6865 6c6c 6f hello
PQgetlength() on char column returns 12 PQgetvlaue() on char column returns 00000000: 5c30 3030 0102 0368 656c 6c6f
\000...hello
are there any functions for escaping and unescaping binary data?
thanks