From: Alex Pilosov <alex@pilosoft.com>
To: Hugh Mandeville <hughmandeville@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Re: Re: Re: binary data
Date: Thu, 28 Jun 2001 13:20:15 -0400 (EDT)
Message-ID: <Pine.BSO.4.10.10106281052040.7004-100000@spider.pilosoft.com> (raw)
In-Reply-To: <9he52u$1061$1@news.tht.net>
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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: alex@pilosoft.com, hughmandeville@hotmail.com
Subject: Re: binary data
In-Reply-To: <Pine.BSO.4.10.10106281052040.7004-100000@spider.pilosoft.com>
References: <9he52u$1061$1@news.tht.net> <Pine.BSO.4.10.10106281052040.7004-100000@spider.pilosoft.com>
* 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