From: Hugh Mandeville <hughmandeville@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Re: Re: binary data
Date: Wed, 27 Jun 2001 22:34:27 -0400
Message-ID: <9he52u$1061$1@news.tht.net> (raw)
References: <200106231402.f5NE2jS03849@candle.pha.pa.us>
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\003
00000010: 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: hughmandeville@hotmail.com
Subject: Re: Re: Re: binary data
In-Reply-To: <9he52u$1061$1@news.tht.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox