Thread: The mysterious BLOB and bytea data types

I need to store some images in my PG database, and it sounds like
bytea might be the way to do it, but there seems to be no
documentation on what it is, how to use it, or anything.  These images
will be no more than 5k bytes in size.  Which type should I use?

Thanks

i use bytea, it is easier than using the large object functions.  you have
to escape your binary data on the way in (to work in the query string) and
unescape it on the way out.
> If you are using Perl DBD::Pg, it's done for you automatically.


i believe these are the escape rules for bytea (binary  -  string):
    0  -  \\000
    \  -  \\\\
    non-printable characters  -  \ooo  (where ooo is their 3 digit octet
value)
    '  -  ''

and these i believe are the unescape rules for bytea (string - binary):
    \ooo  -  N  (where ooo is their 3 digit octet value)
    \\  -  \

test=# CREATE TABLE testbinary ( id serial PRIMARY KEY, data bytea);
test=# INSERT INTO testbinary (data) VALUES
('\\000\001\002Three''Four''\\\\Five');
INSERT 124403 1
test=# select octet_length(data), data FROM testbinary;
 octet_length |             data
--------------+-------------------------------
           19 | \000\001\002Three'Four'\\Five
(1 row)


if you are not worried about space you could base64 encode the data and
store it as a varchar.