Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2009
    Posts
    6

    Unanswered: Insert .jpeg files into bytea column

    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!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    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.

  3. #3
    Join Date
    Feb 2009
    Posts
    6
    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

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    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.

  5. #5
    Join Date
    Feb 2009
    Posts
    6
    That did the trick. Thanks for the help!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •