Re: [GENERAL] Wrong length of bytea field

[Michael Fuhr]

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

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Wrong length of bytea field

[Andrus Moor]

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.




---(end of broadcast)---
TIP 6: explain analyze is your friend