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 have to store .pdf files in a table.

I have a table, state, with columns:

id_state,
name,
pdffile (bytea)

I want to store the pdf files in the pdffile column.

How can I do this?

share|improve this question
1  
... and you're sure you don't want to store it in a file system location and have that location stored in the database (just for clarification and not for flame wars, just trying to ensure bases are covered)? –  swasheck Mar 12 '13 at 15:30
1  
Which programming language do you use? @swasheck: there are perfectly valid reasons to store this kind of things in a database. As with everything it has its advantages and disadvantages (I personally think it has more advantages than disadvantages). –  a_horse_with_no_name Mar 12 '13 at 17:11
    
@a_horse_with_no_name: I know. As I said, I was just ensuring that this was the best course of action for OP's use case. –  swasheck Mar 12 '13 at 17:45

3 Answers 3

First, I store PDF's in the db as bytea's. They are easier to manage than large objects and you really dont get very much out of the streaming API (now, streaming video, that should go into lobs). The one real problem with doing this is that it takes extra memory to unescape the data when it comes back to the database and you really can't stream it effectively.

The key thing is that you have to unescape the data when it comes back. libpq provides functions for that as do other programming languages. A few (like Perl) provide db drivers which automatically handle these cases.

You are on to the right structure here. Store it as a bytea and unescape it on retrieval.

share|improve this answer

To achieve this I have used the library lipq. It's the C API for postgresql.

const char *paramValues[2];
paramValues[0] = &your_id;
paramValues[1] = &binaryContentOfPdfFile;

res = PQexecParams(pConnection, "insert into my_table (id_state, pdffile)
VALUES ($1, $2);",
        2,       /* params */
        NULL,    /* let the backend deduce param type */
        paramValues,
        NULL,    /* don't need param lengths since text */
        NULL,    /* default to all text params */
        0);

Here is the postgresql mailing list thread I took the code from.

I guess it would be something not so different with other API like pyODBC or psqlODBC. It depends on the library you have chosen. You didn't say which API you use.

However, the swasheck's suggestion is also good: you could only store an URI to the pdf file in your database instead.

share|improve this answer

Probably the best way store PDF file in postgresql is via large object. You should have a table field of type OID. The create a large object with your PDF and then store the large object OID in the table.

Be careful with postgresql 9, since large object rights where defined. If you want to share access to the large object to many different postgresql users, you have to GRANT them the SELECT right. Please note that you cannot GRANT the DELETE right, so the owner is the one that must delete the object.

Moreover, special care should be done for maintaining storage: whenever you delete a large object, and you remove the OID from the table, the large object storage is not freed. You should run a command from contrib package, called vacuumlo, anche then the normal vacuumdb.

share|improve this answer

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.