I installed PostgreSQL 9.1 under Ubuntu 12.10

And now I have a table called property

I want to add a field that can be used to store photo(link to the photo or import a photo into a column?)

But I do not know how to add this field

Can PostgreSQL do this?

If so, what keywords can I use to find this information?

Update 2013/10/30

I try to use oid to import my files, and it works fine!

But when I want to use a not superuser user to output files,

it's give me an error

ERROR:  must be superuser to use server-side lo_export()
HINT:  Anyone can use the client-side lo_export() provided by libpq.

How to use client-side lo_export()?

I try to google it, but there's almost all server-side tutorial

So I tried to use byteA

it's works fine with other user but cannot export data to a file?

I tried to use bytea_export but bytea_import is make by Jack's sql script not a built-in function

So there is two more question:

1) How to use clent_side lo_export()

2) how to use bytea_export()??

Thanks for answers!

2 Answers 2


There are two ways to store Large Objects LOBs(like photos and images) in your property table. You can use the BYTEA data type which is limited to 1GB. The other option is OID, which stores large objects in a special LOB structure. The OID column is limited to 2GB.


Here is the relevant documentation from postgres regarding OIDS.

-- Add the column

-- insert the image dilbert.jpg
INSERT INTO property VALUES('dilbert', lo_import('/var/lib/pgsql/dilbert.jpg'));

-- export the image to the tmp directory
SELECT lo_export(property.photo, '/tmp/dilbert.jpg') FROM photos;


Here is a great example from Jack Douglas on how to use a function to import data into a bytea column. Just remember that in order to use the function, you would have to run the following command to activate plpgsql.

psql mydatabase -c 'CREATE LANGUAGE plpgsql;'
psql mydatabase < bytea_import_function.sql 

(where bytea_import_function.sql contains the code from Jack's example.)

Now connect to psql and your database

psql mydatabase

-- Add the column

-- Insert binary data
insert into photos (name,photo) values('dilbert_bytea',bytea_import('/var/lib/pgsql/dilbert.jpg'));

-- Select data 
SELECT name, photo FROM property;

OID has several advantages over BYTEA. BYTEA will preload your images into memory before sending them to the user, whereas an OID column streams the data directly to the user.

  • Or (in my view this is usually the best) store the images in the filesystem and put only some link, path or anything pointing to them into the DB. Commented Oct 29, 2013 at 15:12
  • Hi @dezso, I bumped into Paul's post first where is yours? Commented Oct 29, 2013 at 15:15
  • Very interesting question - if only I knew what you are talking about :) Commented Oct 29, 2013 at 17:58
  • There is a interesting question about store image(or something) in filesystem I have think about it in first place, but there's no datatype about hyper link ,so it's can only shows the path to object but you need to copy the path to the browser,and need ip from ubuntu,not just like "~/photos" path so I barely give it up!any better way to do it? Commented Oct 29, 2013 at 18:54
  • @dezso You mentioned storing images in the file system and just adding a link. blueman is looking for just that. Would you like to add in your own answer with that process? Commented Oct 30, 2013 at 6:08

Don't store images in the database

There are no database features that can edit or process a photo. Storing them is likely to lock up your db-session for extended periods. There is absolutely no reason whatsoever to do this. PostgreSQL is not a filesystem, it's a database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.