Insert an Image in Postgresql Database

Insert image in a database in Postgres

Tip: don't save images in the database, save them on the filesystem and save the path of the image in the database in a text column.

However, if you must save an image you should use bytea column (similar to BLOB in other databases). Use the following command to add a bytea column to an existing table:

alter table_name add column column_name bytea;

Insert image into PostgresSQL

Edit: It's been a while since I answered this question, but I guess I got it wrong and although the OP seemed happy with the answer, I believe it needs a few comments. Thanks to Daniel Vérité!

lo_import

Using lo_import you can import files using absolute paths, but it returns an object of type OID, so you'd need to change your column data type - also proposed by stud3nt in another answer.

INSERT INTO public.tblcast(castname, castimage)
VALUES ('Henry Cavill',lo_import('server_path_to_file'));

If you don't have the luxury of placing the files in the server before import (like the most of us), you can use the \lo_import facility with psql from your console:

echo "\lo_import '/client_path_to_file' \\\ INSERT INTO  public.tblcast VALUES ('Henry Cavil', :LASTOID)" | psql yourdb

lo_export:

psql yourdb -c "SELECT lo_export(castimage, 'path_to_export_file') FROM tblcast;"

pg_read_file (less flexible):

This generic file access function offers the possibility to read files in the server. However, it is limited to the data directory path. If you want to know where it is in your system, try the following command:

SHOW data_directory

And here would be a way of how to use it - as also shown in the answer from Mike Pur:

INSERT INTO public.tblcast(castname, castimage)
VALUES ('Henry Cavill',pg_read_file('path to file')::bytea);

Related Topics