Insert .jpeg files into bytea column

stuntman2625 · #1 (**permalink**) 05-03-11, 16:17

I'm an Oracle DBA with very little PostgreSQL experience. I have a simple requirement:

I have a table with a bytea column in it and a directory on my server with a bunch of .jpeg files. How can I insert these into that table? I've been searching forums for hours and have seen the same question posted dozens of times, but yet to find a single answer. All I see is how to insert .jpeg's into an oid column which isn't what I need.

Could someone please help me?!?!

Thanks!

shammat · #2 (**permalink**) 05-04-11, 03:54

You can insert binary data into a bytea column using a "hex escaping"

Code:

INSERT INTO my_table (id, picture)
VALUES
(42, decode('89504'));

Alternative ways of specifying a "binary literal" are shown in the manual:

http://www.postgresql.org/docs/curre...pe-binary.html

If you already have images in that table, you can use pg_dump (SQL format) to generate the sample SQL statements.

stuntman2625 · #3 (**permalink**) 05-06-11, 14:11

Thanks a lot for the reply! This would work yes, but what I'm really looking for is a way to insert into the bytea field by just referring to the jpeg file rather than having to get the hex value of all of the jpeg's first. Something similar to the following that you can use for oid's:

INSERT INTO table VALUES (lo_import('file.JPG'));

Thanks

shammat · #4 (**permalink**) 05-06-11, 15:37

If you can use a 3rd party tool, you might want to consider SQL Workbench/J (http://www.sql-workbench.net) which has an extension to the regular INSERT and UPDATE statements like the following:

Code:

INSERT INTO image_table (id, image) 
VALUES
(1, {$blobfile='/path/to/image.jpg'});

(http://www.sql-workbench.net/manual/...l#blob-support)
It works well with PostgreSQL and can be used in batch, console or GUI mode.

stuntman2625 · #5 (**permalink**) 05-09-11, 10:55

That did the trick. Thanks for the help!

Internet Services

Web Development

Digital Marketing

Consumer Tech