Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
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. –  dezso Oct 29 '13 at 15:12
    
Hi @dezso, I bumped into Paul's post first where is yours? –  Craig Efrein Oct 29 '13 at 15:15
    
Very interesting question - if only I knew what you are talking about :) –  dezso Oct 29 '13 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? –  blueman010112 Oct 29 '13 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? –  Craig Efrein Oct 30 '13 at 6:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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