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?
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.
To achieve this I have used the library
I guess it would be something not so different with other API like
However, the swasheck's suggestion is also good: you could only store an URI to the pdf file in your database instead.
Probably the best way store PDF file in postgresql is via large object. You should have a table field of type
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
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