PostgeSQL \ lo_import and how to get the resulting OID into the UPDATE command?

I am working with Postgres 9.0 and I have an application where I need to insert images into remote server

. Therefore, I use:

 "C:\Program Files\PostgreSQL\9.0\bin\psql.exe" -h -p 5432 -d myDB -U my_admin -c  "\lo_import 'C://im/zzz4.jpg'";



is the IP address of the server system


- port number


- server database name


is the username

"\lo_import 'C://im/zzz4.jpg'"

is the request that is being run.

Shell screenshot

After the image has been inserted into the database, I need to update the row in the table like this:

UPDATE species 
SET    speciesimages=17755;  -- OID from previous command.. how to get the OID ??
WHERE  species='ACCOAA';


So my question is, how do I return OID

after \lo_import

in psql?

I tried to run \lo_import 'C://im/zzz4.jpg'

in Postgres, but I get an error:

ERROR:  syntax error at or near ""\lo_import 'C://im/zzz4.jpg'""
LINE 1: "\lo_import 'C://im/zzz4.jpg'"


I also tried this:

update species
set speciesimages=\lo_import 'C://im/zzz4.jpg'
where species='ACAAC04';


But I am getting this error:

ERROR:  syntax error at or near "\"
LINE 2: set speciesimages=\lo_import 'C://im/zzz4.jpg'



source to share

2 answers

Since your file is on your local machine and you want to import the blob to a remote server, you have two possibilities:

1) Transfer the file to the server and use the server function :

UPDATE species
SET    speciesimages = lo_import('/path/to/server-local/file/zzz4.jpg')
WHERE  species = 'ACAAC04';


2) Use psql meta command as if you have.

But you cannot mix psql meta commands with SQL commands, which is not possible.
Use the psql variable :LASTOID

in the command UPDATE

you run right after the meta command \lo_import

in the same psql session:

UPDATE species
SET    speciesimages = :LASTOID
WHERE  species = 'ACAAC04';


In a script that (works on Linux, I'm 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 -p 5432 -d myDB -U my_admin


  • \\

    - separator meta command. You need to double the line \

    per line ""

    because the shell interprets one level.
  • \

    before the newline is a line continuation in Linux shells.

Alternative syntax (tested on Linux again):

psql -h -p 5432 -d myDB -U my_admin << EOF
\lo_import '/path/to/my/file/zzz4.jpg'
UPDATE species
SET    speciesimages = :LASTOID
WHERE  species = 'ACAAC04';




After importing the image using this command:

\lo_import '$imagePath' '$imageName'


Then you can find the description of the binary by pg_catalog.pg_largeobject_metadata

querying the table that stores the desired oid value.


"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 "




All Articles