Re: Deleting BLOBs

On Sun, 12 Aug 2012, Neanderthelle Jones wrote:

> I'd like to delete all the image files from a table by issuing an
> interactive psql command. Given
>
> CREATE TABLE image (
> name text,
> rast oid
> );
>
> with most tuples having images in rast, wanna do something like
>
> => select lo_unlink('select rast from image');
>
> Can such a thing be done, and how? I guess I've forgotten the syntax.
>
> Or will an update that sets the oids to null or 0 work?
>
> (pgsql 9.1.2)

One attempt. Is the error mine or PostgreSQL's?

$ for i in $(psql -q -t -U elle -d my_db \
-c "SELECT raster FROM images where raster > 0"); do
echo $i
psql -q -U elle -d my_db -c "SELECT lo_unlink($i)"
done

21234
ERROR: large object 21234 does not exist
21235
ERROR: large object 21235 does not exist
21236
ERROR: large object 21236 does not exist
21237
ERROR: large object 21237 does not exist
21238
ERROR: large object 21238 does not exist
21239
ERROR: large object 21239 does not exist
21240
ERROR: large object 21240 does not exist
21241
ERROR: large object 21241 does not exist
21242
ERROR: large object 21242 does not exist
21243
ERROR: large object 21243 does not exist

etc. etc.