pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (12+ messages)

Message-ID: <Pine.BSO.4.10.10106281052040.7004-100000@spider.pilosoft.com>
Permalink:  ../Pine.BSO.4.10.10106281052040.7004-100000@spider.pilosoft.com/
Also on:    postgresql.org/message-id/Pine.BSO.4.10.10106281052040.7004-100000@spider.pilosoft.com

 · 

reply

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