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

> > 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(at)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