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





view thread (12+ messages)  latest in thread

Message-ID: <9he52u$1061$1@news.tht.net>
Permalink:  ../9he52u$1061$1@news.tht.net/
Also on:    postgresql.org/message-id/9he52u$1061$1@news.tht.net

 · 

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 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