CODEWITHSUNDEEP
pythonsqlpostgresqlimage
ReaL_HyDRA
ReaL_HyDRA

Reputation: 363

Insert image into PostgresSQL

I was trying to insert into a postgres database. This is the Query

INSERT INTO public.tblcast(
    castname, castimage)
    VALUES ('Henry Cavill',bytea('E:\Cast\henry.jpg'));

But it shows an error 

ERROR:  invalid input syntax for type bytea
LINE 3:  VALUES ('Henry Cavill',bytea('E:\Cast\henry.jpg'));
                                      ^
SQL state: 22P02
Character: 81ERROR:  invalid input syntax for type bytea

The column castimage is with data type bytea.

Upvotes: 2

Views: 1099

Answers (3)

Jim Jones
Jim Jones

Reputation: 19653

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);

Upvotes: 1

Mike Pur
Mike Pur

Reputation: 41

Use pg_read_file

INSERT INTO public.tblcast(
    castname, castimage)
    VALUES ('Henry Cavill',pg_read_file('E:\Cast\henry.jpg')::bytea);

Upvotes: 2

stud3nt
stud3nt

Reputation: 2143

Use lo_import('E:\Cast\henry.jpg') instead of bytea('E:\Cast\henry.jpg')

Upvotes: 1

Related Questions