Re: Storing small image files

Nelson Green wrote on 09.05.2013 19:05:
>>On Thu, May 9, 2013 at 10:51 AM, Achilleas Mantzios wrote:
>> then here :
>> http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html
>
> Thanks Achilleas. I usually do the physical design in vi using sql
> scripts, and I like to include a couple of inserts and selects to
> make sure everything is going according to plan. It looks like I may
> just have to work with a front-end developer for this particular
> instance. Of all the stupid things, in all of my years doing this
> I've never once had to work with storing binary files, other than
> years ago when I was studying for some of the MySQL certs.

The thread from DbForums links to the SQL tool I'm maintaining, SQL Workbench/J: http://www.sql-workbench.net

I assume the image files are stored on the client where you run the SQL rather than on the Postgres server, right?

If you can use a different SQL client than psql, then SQL Workbench is probably the easiest way to solve this.
I added that "extended" (proprietary) SQL syntax exactly for this purpose.

Your statement would become:

INSERT INTO security_badge
VALUES
(
'PK00880918',
(SELECT employee_id
FROM employee
WHERE employee_lastname = 'Kendell' AND
employee_firstname = 'Paul'),
{$blobfile='/path/to/test.jpg'}
);

The /path/to/test.jpg is local to the computer where SQL Workbench is running.

SQL Workbench is not only usable as a GUI application but also in console mode (similar to psql then) or in batch mode to run the scripts automatically.

For bulk loading the SQL Workbench specific "WbImport" command also supports text files that contain a filename to be stored into a bytea column.

All this support is for bytea only, it does not support "large objects" but as you are storing "small images", bytea is the better choice anyway.

Regards
Thomas

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general