Wrong length of bytea field
I inserted two binary files to a bytea field "contents"
file1 size was 7834649 bytes
file2 size was 5888534 bytes
select filename,
octet_length(contents),
length(contents)
from localfil
returns
"file1 ";16777184;16777184
"file2 ";15768893;15768893
It seems that for some reason postgres returns sometimes escaped string size
(eq. single byte 0x00 uploaded as \\000 size is returned as 5 )
Why result is incorrect ?
How to get the actual size of bytea field ?
Using Postgres 8.1 in XP , cluster encoding is UTF-8
Andrus.
On Fri, Feb 24, 2006 at 10:32:58PM +0200, Andrus Moor wrote:
It seems that for some reason postgres returns sometimes escaped string size
(eq. single byte 0x00 uploaded as \\000 size is returned as 5 )
I'd guess that "some reason" is because the data was over-escaped
when it was inserted -- that is, escaped and then escaped again.
Try inserting a small file and querying for the bytea column and
see what you get.
CREATE TABLE foo (data bytea);
INSERT INTO foo VALUES ($$\000$$);
INSERT INTO foo VALUES ($$\\000$$);
INSERT INTO foo VALUES ($$\\\000$$);
INSERT INTO foo VALUES ($$\\\\000$$);
SELECT data, octet_length(data) FROM foo;
data | octet_length
---------+--------------
\000 | 1
\\000 | 4
\\\000 | 2
\\\\000 | 5
(4 rows)
--
Michael Fuhr