How can I store a pdf in PostgreSQL

Question

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?

Answer 1

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.

Answer 2

Don't store PDFs in the database

There are no database features that can edit or process a PDF. 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.

Answer 3

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: you should also consider storing only the URI/URL to the pdf file in your database.

Answer 4

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.