http://www.perlmonks.org?node_id=522106


in reply to postgresql bytea escaping

Sure it is difficult inserting binary data, but you can avoid it! Use the lo* family of commands that will manage (upload/download, read/write/seek, etc.,) binary files to postgresql; alternatively and preferably, use the equivalent \lo-import, \lo_export, and \lo_list commands from psql.

These tools is more than enough for the job. If you want more tools to manage binary data, use the lo.sql and lo_drop.sql from the contrib section.

Furthermore, you can always avoid dealing with binary data if you encode them to ascii (say using base64) and don't mind a little extra storage space.

Oh, and you might also choose to avoid both methods (both the binary or ascii data) by using links or references to files on disk instead of storing the data inside the database.

Now you better alternatives.

Replies are listed 'Best First'.
Re^2: postgresql bytea escaping
by ketema (Scribe) on Jan 11, 2006 at 02:10 UTC
    Don't the lo family functions create OID in your pg database, which are references to the file system? I chose bytea, because I know the binary data will typically be less the 10 mb, and I didn't want to have the extra mangement headache of keeping the external file system synced. If I am wrong in understanding the way the lo functions work, please correct me.
      The created OID is a reference to pg_largeobject.loid, the table where the binary data are stored. The data are already copied into the database.