Thread: Exporting a PDF from a bytea column
Hi all; we have inherited a legacy database. The system stores PDF's as large objects in bytea columns. Can anyone send me an example of exporting from a bytea column to a PDF file? Thanks in advance
On 2/18/2016 4:44 PM, CS DBA wrote: > The system stores PDF's as large objects > in bytea columns. Large Objects aka LO's and bytea columns are two completely different things. bytea columns are regular SQL database columns that contain a binary byte array. large objects (LO) are a whole separate facility, where each object is referenced by a unique OID, and the object is read and written with the lo_XXX() functions in libpq, or equivalents in other language specific bindings. http://www.postgresql.org/docs/current/static/largeobjects.html in a C program, an LO can be exported to a file via the API lo_export() http://www.postgresql.org/docs/current/static/lo-interfaces.html#LO-EXPORT > Can anyone send me an example of > exporting from a bytea column to a PDF file? I don't think you can get from a bytea field to a file without some coding, as SQL scripting doesn't handle binary blobs very well. -- john r pierce, recycling bits in santa cruz
On Thursday, February 18, 2016, John R Pierce <pierce@hogranch.com> wrote:
On 2/18/2016 4:44 PM, CS DBA wrote:The system stores PDF's as large objects
in bytea columns.
Large Objects aka LO's and bytea columns are two completely different things.
I'll assume the "column" is the most relevant term here because the above is true.
Can anyone send me an example of
exporting from a bytea column to a PDF file?
I don't think you can get from a bytea field to a file without some coding, as SQL scripting doesn't handle binary blobs very well.
Short answer, to avoid the binary blob problem, is to encode the binary data, export it, then decode it.
This can be done is psql. If your client can handle binary directly (e.g, JDBC/Java) you can use that language's facilities to perform the binary transfer directly thus bypassing the need to transcode.
David J,
On 02/18/2016 07:29 PM, David G. Johnston wrote:
On Thursday, February 18, 2016, John R Pierce <pierce@hogranch.com> wrote:Can it be done from a Linux shell script? Any examples? Seems to be little info on this in my googling?On 2/18/2016 4:44 PM, CS DBA wrote:The system stores PDF's as large objects
in bytea columns.
Large Objects aka LO's and bytea columns are two completely different things.I'll assume the "column" is the most relevant term here because the above is true.Can anyone send me an example of
exporting from a bytea column to a PDF file?
I don't think you can get from a bytea field to a file without some coding, as SQL scripting doesn't handle binary blobs very well.Short answer, to avoid the binary blob problem, is to encode the binary data, export it, then decode it.This can be done is psql. If your client can handle binary directly (e.g, JDBC/Java) you can use that language's facilities to perform the binary transfer directly thus bypassing the need to transcode.
David J,
Can it be done from a Linux shell script? Any examples? Seems to be little info on this in my googling?Short answer, to avoid the binary blob problem, is to encode the binary data, export it, then decode it.This can be done is psql. If your client can handle binary directly (e.g, JDBC/Java) you can use that language's facilities to perform the binary transfer directly thus bypassing the need to transcode.
Others may provide actual examples but at the moment I don't have the time to explore to that depth.
To answer your "shell script" question...anything can be done in a shell script - as long as you have the right programs on your system to do the actual work. I already mentioned "psql" which, if you want the least amount of pure "coding", is going to be the helper program you will want to use. And its usage is well documented. I would suggest base64 encoding and then using whatever standard base64 decoder program exists on your Linux box to perform the decoding.
David J.