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.
