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