X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 34B179DC812
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 20 Mar 2006 10:19:21 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 88167-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 20 Mar 2006 10:19:25 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from peufeu.com (boutiquenumerique.com [82.67.9.10])
	by postgresql.org (Postfix) with ESMTP id 85E499DC87F
	for <pgsql-sql@postgresql.org>; Mon, 20 Mar 2006 10:19:17 -0400 (AST)
Received: (qmail 16706 invoked from network); 20 Mar 2006 15:19:47 +0100
Received: from boutiquenumerique.com (HELO apollo13) (82.67.9.10)
	by boutiquenumerique.com with SMTP; 20 Mar 2006 15:19:47 +0100
Date: Mon, 20 Mar 2006 15:19:46 +0100
To: "Eugene E." <sad@bankir.ru>,
	"Achilleus Mantzios" <achill@matrix.gatewaynet.com>
Subject: Re: have you feel anything when you read this ?
Cc: "Peter Eisentraut" <peter_e@gmx.net>, pgsql-sql@postgresql.org
References: <Pine.LNX.4.44.0603201522410.22894-100000@matrix.gatewaynet.com>
	<441EA0A7.8030601@bankir.ru>
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <op.s6pt28eacigqcu@apollo13>
In-Reply-To: <441EA0A7.8030601@bankir.ru>
User-Agent: Opera M2/8.51 (Linux, build 1462)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.11 required=5 tests=[AWL=0.110]
X-Spam-Score: 0.11
X-Spam-Level: 
X-Archive-Number: 200603/305
X-Sequence-Number: 24651


> the problem is: you'll get this four byte sequence '\000' _instead_ of  
> NUL-byte anyway.


http://www.postgresql.org/docs/8.1/interactive/datatype-binary.html says :

"A binary string is a sequence of octets (or bytes). Binary strings are  
distinguished from character strings by two characteristics: First, binary  
strings specifically allow storing octets of value zero and other  
"non-printable" octets (usually, octets outside the range 32 to 126).  
Character strings disallow zero octets, and also disallow any other octet  
values and sequences of octet values that are invalid according to the  
database's selected character set encoding. Second, operations on binary  
strings process the actual bytes, whereas the processing of character  
strings depends on locale settings. In short, binary strings are  
appropriate for storing data that the programmer thinks of as "raw bytes",  
whereas character strings are appropriate for storing text."

That's the whole point of escaping, so that data generated by COPY, for  
instance, does not include any funky characters, including the \0 (zero)  
character, so that you can use any standard tool on it, including grep...

I LIKE the fact that TEXT refuses invalid characters. It helps find bugs  
in my applications, like when I forget to process some 8-bit string before  
inserting it in my database which uses UNICODE. I definitely prefer an  
error than finding a month later that half my data has had all its  
accented characters screwed up.

in psql, you have to use the escaped syntax :

SELECT length('\\000'::BYTEA), length('\\001'::BYTEA),  
length('\\000'::TEXT), length('\\001'::TEXT);
  length | length | length | length
--------+--------+--------+--------
       1 |      1 |      4 |      4

Your client library should take care of escaping and de-escaping. Here, in  
python :

>>> cursor.execute( "SELECT %s,%s::BYTEA", ("this is a normal string",  
>>> psycopg2.Binary( ">\x00< this is a string with a zero byte" ),) )
>>> r = cursor.fetchone()
>>> print r
['this is a normal string', <read-only buffer for 0x2aaaaab32210, size 37,  
offset 0 at 0x2aaaace27c70>]
>>> print str(r[1])
> < this is a string with a zero byte
>>> print repr(str(r[1]))
'>\x00< this is a string with a zero byte'
>>> ord(r[1][1])
0


Note : \x00 is python's escaping for the null byte