PostgreSQL \lo_import and how to get the resulting OID into an UPDATE command?
As your file resides on your local machine and you want to import the blob to a remote server, you have two options:
1) Transfer the file to the server and use the server-side function:
UPDATE speciesSET speciesimages = lo_import('/path/to/server-local/file/zzz4.jpg')WHERE species = 'ACAAC04';
2) Use the psql meta-command like you have it.
But you cannot mix psql meta commands with SQL-commands, that's impossible.
Use the psql variable :LASTOID
in an UPDATE
command that you launch immediately after the \lo_import
meta command in the same psql session:
UPDATE speciesSET speciesimages = :LASTOIDWHERE species = 'ACAAC04';
To script that (works in Linux, I am not familiar with Windows shell scripting):
echo "\lo_import '/path/to/my/file/zzz4.jpg' \\\\ UPDATE species SET speciesimages = :LASTOID WHERE species = 'ACAAC04';" | \psql -h 192.168.1.12 -p 5432 -d myDB -U my_admin
\\
is the separator meta-command. You need to double the\
, in a""
string, because the shell interprets one layer.\
before the newline is just the line continuation in Linux shells.
Alternative syntax (tested on Linux again):
psql -h 192.168.1.12 -p 5432 -d myDB -U my_admin << EOF\lo_import '/path/to/my/file/zzz4.jpg'UPDATE speciesSET speciesimages = :LASTOIDWHERE species = 'ACAAC04';EOF
After importing an image with this command:
\lo_import '$imagePath' '$imageName'
You can then find the description of the binary by querying the pg_catalog.pg_largeobject_metadata
table which stores the oid value you need.
Ie:
"SELECT oid as `"ID`",pg_catalog.obj_description(oid, 'pg_largeobject') as `"Description`"FROM pg_catalog.pg_largeobject_metadata WHERE pg_catalog.obj_description(oid,'pg_largeobject') = '$image' limit 1 "
Here's how to do it if your field is type bytea
.
\lo_import '/cygdrive/c/Users/Chloe/Downloads/Contract.pdf'update contracts set contract = lo_get(:LASTOID) where id = 77;
Use \lo_list
and \lo_unlink
after you import.