Thread: Bug #467: Can't insert a value of 0 (zero) into a Bytea type.
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?
Itseems 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
4bytes) 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
by3 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
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
of4 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
followedby 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