Add a column contains a photo

Question

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!

Answer 1

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.

OID

Here is the relevant documentation from postgres regarding OIDS.

-- Add the column
ALTER TABLE property ADD COLUMN photo OID;

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

BYTEA

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
ALTER TABLE property ADD COLUMN photo BYTEA;

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

Answer 2

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.