Bug #467: Can't insert a value of 0 (zero) into a Bytea type.

pgsql-bugs@postgresql.org wrote:

Billy G. Allie (Bill.Allie@mug.org) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Can't insert a value of 0 (zero) into a Bytea type.

Long Description
It does not zeem possible to insert a value of zero (0) into a bytea type. A
lso, using '\134' (the octal code for a backslash) causes byteain() to genera
te an error message.

As a side issue, how can one tell a backslash followed by 3 digits (four byte
s of data) from an encoded byte of data? It seems to me that byteaout() shou
ld always output an octal escape sequence per byte, even if the character is
printable. That way the result is unambiguous in meaning (even if it is wast
eful of bytes).

Further investigation provided the following information:

1. To insert a zero value the '\\000' sequence is required.

2. To insert a backslash, 4 backslashes are required (i.e. '\\\\')

Therefore, to insert a backslash followed by the characters 1, 2, and 3 (four bytes of data), you would uses the sequence '\\\\123'. On retrieval from the database, the sequence '\\123' would be returned.

Can anyone confirm that this is correct. If it is, then this bug report can be closed.

Thanks.
--
____ | Billy G. Allie | Domain....: Bill.Allie@mug.org
| /| | 7436 Hartwell | MSN.......: B_G_Allie@email.msn.com
|-/-|----- | Dearborn, MI 48126|
|/ |LLIE | (313) 582-1540 |

Short Description
Can't insert a value of 0 (zero) into a Bytea type.

Long Description
It does not zeem possible to insert a value of zero (0) into a bytea

type. A

lso, using '\134' (the octal code for a backslash) causes byteain() to

genera

te an error message.

As a side issue, how can one tell a backslash followed by 3 digits (four

byte

s of data) from an encoded byte of data? It seems to me that byteaout()

shou

ld always output an octal escape sequence per byte, even if the

character is

printable. That way the result is unambiguous in meaning (even if it is

wast

eful of bytes).

Further investigation provided the following information:

1. To insert a zero value the '\\000' sequence is required.

2. To insert a backslash, 4 backslashes are required (i.e. '\\\\')

Therefore, to insert a backslash followed by the characters 1, 2, and 3

(four

bytes of data), you would uses the sequence '\\\\123'. On retrieval from

the

database, the sequence '\\123' would be returned.

Can anyone confirm that this is correct. If it is, then this bug report

can be closed.

This was recently discussed on hackers (see
http://fts.postgresql.org/db/mw/msg.html?mid=1032591), but the short answer
is that you are correct (and that this is not a bug).

[root@jec-linux /root]# psql -U postgres test
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

test=# create table t1(f1 bytea);
CREATE
test=# insert into t1(f1) values('\\\\123');
INSERT 1482289 1
test=# select f1 from t1;
f1
-------
\\123
(1 row)

test=# select octet_length(f1) from t1;
octet_length
--------------
4
(1 row)

test=# insert into t1(f1) values('\\000');
INSERT 1482290 1
test=# select f1 from t1 where f1 = '\\000';
f1
------
\000
(1 row)

test=# select octet_length(f1) from t1 where f1 = '\\000';
octet_length
--------------
1
(1 row)

HTH,

-- Joe

This is fixed in the current CVS tree.

Billy G. Allie (Bill.Allie@mug.org) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Can't insert a value of 0 (zero) into a Bytea type.

Long Description
It does not zeem possible to insert a value of zero (0) into a bytea type. Also, using '\134' (the octal code for a backslash) causes byteain() to generate an error message.

As a side issue, how can one tell a backslash followed by 3 digits (four bytes of data) from an encoded byte of data? It seems to me that byteaout() should always output an octal escape sequence per byte, even if the character is printable. That way the result is unambiguous in meaning (even if it is wasteful of bytes).

Sample Code
bga=# select version();
version
-----------------------------------------------------------
PostgreSQL 7.1.3 on i586-sco-sysv5uw7.1.1, compiled by cc
(1 row)
bga=# create table test6 (a bytea);
CREATE
bga=# insert into test6 values('\001\002\354ab\000de\004');
INSERT 163734 1
bga=# select * from test6;
a
----------------
\001\002\354ab
(1 row)

Note: It is possible to workaround (i.e. fake out postgresSQL) by inserting a backslash followsd by 3 zeros (a total of 4 bytes) into the bytea type. For example:

bga=# insert into test6 values('\001\002\354ab\\000de\004');
INSERT 163735 1
bga=# select * from test6;
a
--------------------------
\001\002\354ab
\001\002\354ab\000de\004
(2 rows)

This also illustrates another problem with bytea types. How does one tell the difference between a backslash followed by 3 digits (four bytes of data) for an encoded (single) byte of data?

The problem with using '\134' as input to a bytea field is illustrated here:

bga=# insert into test6 values('\134');
ERROR: Bad input string for type bytea
bga=#

No file was uploaded with this report

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026