How can I store a pdf in PostgreSQL












5















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




















  • 2





    ... 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








  • 2





    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








  • 1





    @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
















5















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




















  • 2





    ... 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








  • 2





    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








  • 1





    @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














5












5








5


2






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
















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?







postgresql insert blob






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited May 25 '17 at 4:17









Evan Carroll

32.6k970222




32.6k970222










asked Mar 12 '13 at 15:18









GiuliocasGiuliocas

26112




26112








  • 2





    ... 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








  • 2





    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








  • 1





    @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














  • 2





    ... 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








  • 2





    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








  • 1





    @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








2




2





... 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







... 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






2




2





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







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






1




1





@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





@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










4 Answers
4






active

oldest

votes


















2














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
























  • This is a very abbreviated list of downfalls. It's not just streaming. It's the backend session stays tied while it's sending the file down to libpq as part of the resultset. Also, whatever other data is left on the row is likely also not ready to display until the PDF transfers. Parallel operations like https pipelining on pdf? You'll now have to tie up the database with one session per PDF.

    – Evan Carroll
    May 26 '17 at 8:17













  • But those are very use-specific downfalls. They don't apply, for example, where you are storing data for specific reasons where those are not applicable. For example wanting to store PDFs actually sent to a customer for tracking purposes. There are OLTP reasons why one might want to do so in an OLTP system.

    – Chris Travers
    May 26 '17 at 8:27



















2














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.






share|improve this answer

































    1














    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































      0














      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.






      share|improve this answer



















      • 1





        I disagree with you. PDFs are just values and being able to back up pdfs attached to database entities in the same backup is a good reason you might want to.

        – Chris Travers
        May 26 '17 at 7:45











      • @ChrisTravers They're not just values: they're files. You can't seek through a value. You can always seek through a file. A value is a piece of data. A file, especially a pdf file, is an input for an external program. A database does not manage the display, a PDF encompasses display. Your RDBMS is not a backup management tool. You can easily back up the database and the filesystem at the same time.

        – Evan Carroll
        May 26 '17 at 8:04













      • If you want to seek, lobs in Postgres give you that. Usually it is not what you want in a db however.

        – Chris Travers
        May 26 '17 at 8:17






      • 2





        So have an accounting system. Want to track invoice pdfs as sent out, and guarantee that these are properly tracked to the transactions for auditing reasons. Why not store them in PostgreSQL. All we need to do is store them, retrieve them, and most importantly back up and restore them with the rest of the data? I.e. they are values for auditing purposes.

        – Chris Travers
        May 26 '17 at 8:24






      • 1





        No reason whatsoever is a dangerous statement and almost always wrong.

        – Chris Travers
        May 26 '17 at 8:25











      Your Answer








      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "182"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: false,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: null,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f36493%2fhow-can-i-store-a-pdf-in-postgresql%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      4 Answers
      4






      active

      oldest

      votes








      4 Answers
      4






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      2














      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
























      • This is a very abbreviated list of downfalls. It's not just streaming. It's the backend session stays tied while it's sending the file down to libpq as part of the resultset. Also, whatever other data is left on the row is likely also not ready to display until the PDF transfers. Parallel operations like https pipelining on pdf? You'll now have to tie up the database with one session per PDF.

        – Evan Carroll
        May 26 '17 at 8:17













      • But those are very use-specific downfalls. They don't apply, for example, where you are storing data for specific reasons where those are not applicable. For example wanting to store PDFs actually sent to a customer for tracking purposes. There are OLTP reasons why one might want to do so in an OLTP system.

        – Chris Travers
        May 26 '17 at 8:27
















      2














      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
























      • This is a very abbreviated list of downfalls. It's not just streaming. It's the backend session stays tied while it's sending the file down to libpq as part of the resultset. Also, whatever other data is left on the row is likely also not ready to display until the PDF transfers. Parallel operations like https pipelining on pdf? You'll now have to tie up the database with one session per PDF.

        – Evan Carroll
        May 26 '17 at 8:17













      • But those are very use-specific downfalls. They don't apply, for example, where you are storing data for specific reasons where those are not applicable. For example wanting to store PDFs actually sent to a customer for tracking purposes. There are OLTP reasons why one might want to do so in an OLTP system.

        – Chris Travers
        May 26 '17 at 8:27














      2












      2








      2







      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













      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












      share|improve this answer



      share|improve this answer










      answered Mar 20 '13 at 10:09









      Chris TraversChris Travers

      11k3889




      11k3889













      • This is a very abbreviated list of downfalls. It's not just streaming. It's the backend session stays tied while it's sending the file down to libpq as part of the resultset. Also, whatever other data is left on the row is likely also not ready to display until the PDF transfers. Parallel operations like https pipelining on pdf? You'll now have to tie up the database with one session per PDF.

        – Evan Carroll
        May 26 '17 at 8:17













      • But those are very use-specific downfalls. They don't apply, for example, where you are storing data for specific reasons where those are not applicable. For example wanting to store PDFs actually sent to a customer for tracking purposes. There are OLTP reasons why one might want to do so in an OLTP system.

        – Chris Travers
        May 26 '17 at 8:27



















      • This is a very abbreviated list of downfalls. It's not just streaming. It's the backend session stays tied while it's sending the file down to libpq as part of the resultset. Also, whatever other data is left on the row is likely also not ready to display until the PDF transfers. Parallel operations like https pipelining on pdf? You'll now have to tie up the database with one session per PDF.

        – Evan Carroll
        May 26 '17 at 8:17













      • But those are very use-specific downfalls. They don't apply, for example, where you are storing data for specific reasons where those are not applicable. For example wanting to store PDFs actually sent to a customer for tracking purposes. There are OLTP reasons why one might want to do so in an OLTP system.

        – Chris Travers
        May 26 '17 at 8:27

















      This is a very abbreviated list of downfalls. It's not just streaming. It's the backend session stays tied while it's sending the file down to libpq as part of the resultset. Also, whatever other data is left on the row is likely also not ready to display until the PDF transfers. Parallel operations like https pipelining on pdf? You'll now have to tie up the database with one session per PDF.

      – Evan Carroll
      May 26 '17 at 8:17







      This is a very abbreviated list of downfalls. It's not just streaming. It's the backend session stays tied while it's sending the file down to libpq as part of the resultset. Also, whatever other data is left on the row is likely also not ready to display until the PDF transfers. Parallel operations like https pipelining on pdf? You'll now have to tie up the database with one session per PDF.

      – Evan Carroll
      May 26 '17 at 8:17















      But those are very use-specific downfalls. They don't apply, for example, where you are storing data for specific reasons where those are not applicable. For example wanting to store PDFs actually sent to a customer for tracking purposes. There are OLTP reasons why one might want to do so in an OLTP system.

      – Chris Travers
      May 26 '17 at 8:27





      But those are very use-specific downfalls. They don't apply, for example, where you are storing data for specific reasons where those are not applicable. For example wanting to store PDFs actually sent to a customer for tracking purposes. There are OLTP reasons why one might want to do so in an OLTP system.

      – Chris Travers
      May 26 '17 at 8:27













      2














      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.






      share|improve this answer






























        2














        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.






        share|improve this answer




























          2












          2








          2







          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.






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







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited 20 mins ago

























          answered Mar 12 '13 at 16:48









          Stephane RollandStephane Rolland

          2,30562136




          2,30562136























              1














              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




























                1














                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


























                  1












                  1








                  1







                  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













                  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












                  share|improve this answer



                  share|improve this answer










                  answered Mar 12 '13 at 23:45









                  eppesuigeppesuig

                  2,7571814




                  2,7571814























                      0














                      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.






                      share|improve this answer



















                      • 1





                        I disagree with you. PDFs are just values and being able to back up pdfs attached to database entities in the same backup is a good reason you might want to.

                        – Chris Travers
                        May 26 '17 at 7:45











                      • @ChrisTravers They're not just values: they're files. You can't seek through a value. You can always seek through a file. A value is a piece of data. A file, especially a pdf file, is an input for an external program. A database does not manage the display, a PDF encompasses display. Your RDBMS is not a backup management tool. You can easily back up the database and the filesystem at the same time.

                        – Evan Carroll
                        May 26 '17 at 8:04













                      • If you want to seek, lobs in Postgres give you that. Usually it is not what you want in a db however.

                        – Chris Travers
                        May 26 '17 at 8:17






                      • 2





                        So have an accounting system. Want to track invoice pdfs as sent out, and guarantee that these are properly tracked to the transactions for auditing reasons. Why not store them in PostgreSQL. All we need to do is store them, retrieve them, and most importantly back up and restore them with the rest of the data? I.e. they are values for auditing purposes.

                        – Chris Travers
                        May 26 '17 at 8:24






                      • 1





                        No reason whatsoever is a dangerous statement and almost always wrong.

                        – Chris Travers
                        May 26 '17 at 8:25
















                      0














                      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.






                      share|improve this answer



















                      • 1





                        I disagree with you. PDFs are just values and being able to back up pdfs attached to database entities in the same backup is a good reason you might want to.

                        – Chris Travers
                        May 26 '17 at 7:45











                      • @ChrisTravers They're not just values: they're files. You can't seek through a value. You can always seek through a file. A value is a piece of data. A file, especially a pdf file, is an input for an external program. A database does not manage the display, a PDF encompasses display. Your RDBMS is not a backup management tool. You can easily back up the database and the filesystem at the same time.

                        – Evan Carroll
                        May 26 '17 at 8:04













                      • If you want to seek, lobs in Postgres give you that. Usually it is not what you want in a db however.

                        – Chris Travers
                        May 26 '17 at 8:17






                      • 2





                        So have an accounting system. Want to track invoice pdfs as sent out, and guarantee that these are properly tracked to the transactions for auditing reasons. Why not store them in PostgreSQL. All we need to do is store them, retrieve them, and most importantly back up and restore them with the rest of the data? I.e. they are values for auditing purposes.

                        – Chris Travers
                        May 26 '17 at 8:24






                      • 1





                        No reason whatsoever is a dangerous statement and almost always wrong.

                        – Chris Travers
                        May 26 '17 at 8:25














                      0












                      0








                      0







                      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.






                      share|improve this answer













                      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.







                      share|improve this answer












                      share|improve this answer



                      share|improve this answer










                      answered May 25 '17 at 4:16









                      Evan CarrollEvan Carroll

                      32.6k970222




                      32.6k970222








                      • 1





                        I disagree with you. PDFs are just values and being able to back up pdfs attached to database entities in the same backup is a good reason you might want to.

                        – Chris Travers
                        May 26 '17 at 7:45











                      • @ChrisTravers They're not just values: they're files. You can't seek through a value. You can always seek through a file. A value is a piece of data. A file, especially a pdf file, is an input for an external program. A database does not manage the display, a PDF encompasses display. Your RDBMS is not a backup management tool. You can easily back up the database and the filesystem at the same time.

                        – Evan Carroll
                        May 26 '17 at 8:04













                      • If you want to seek, lobs in Postgres give you that. Usually it is not what you want in a db however.

                        – Chris Travers
                        May 26 '17 at 8:17






                      • 2





                        So have an accounting system. Want to track invoice pdfs as sent out, and guarantee that these are properly tracked to the transactions for auditing reasons. Why not store them in PostgreSQL. All we need to do is store them, retrieve them, and most importantly back up and restore them with the rest of the data? I.e. they are values for auditing purposes.

                        – Chris Travers
                        May 26 '17 at 8:24






                      • 1





                        No reason whatsoever is a dangerous statement and almost always wrong.

                        – Chris Travers
                        May 26 '17 at 8:25














                      • 1





                        I disagree with you. PDFs are just values and being able to back up pdfs attached to database entities in the same backup is a good reason you might want to.

                        – Chris Travers
                        May 26 '17 at 7:45











                      • @ChrisTravers They're not just values: they're files. You can't seek through a value. You can always seek through a file. A value is a piece of data. A file, especially a pdf file, is an input for an external program. A database does not manage the display, a PDF encompasses display. Your RDBMS is not a backup management tool. You can easily back up the database and the filesystem at the same time.

                        – Evan Carroll
                        May 26 '17 at 8:04













                      • If you want to seek, lobs in Postgres give you that. Usually it is not what you want in a db however.

                        – Chris Travers
                        May 26 '17 at 8:17






                      • 2





                        So have an accounting system. Want to track invoice pdfs as sent out, and guarantee that these are properly tracked to the transactions for auditing reasons. Why not store them in PostgreSQL. All we need to do is store them, retrieve them, and most importantly back up and restore them with the rest of the data? I.e. they are values for auditing purposes.

                        – Chris Travers
                        May 26 '17 at 8:24






                      • 1





                        No reason whatsoever is a dangerous statement and almost always wrong.

                        – Chris Travers
                        May 26 '17 at 8:25








                      1




                      1





                      I disagree with you. PDFs are just values and being able to back up pdfs attached to database entities in the same backup is a good reason you might want to.

                      – Chris Travers
                      May 26 '17 at 7:45





                      I disagree with you. PDFs are just values and being able to back up pdfs attached to database entities in the same backup is a good reason you might want to.

                      – Chris Travers
                      May 26 '17 at 7:45













                      @ChrisTravers They're not just values: they're files. You can't seek through a value. You can always seek through a file. A value is a piece of data. A file, especially a pdf file, is an input for an external program. A database does not manage the display, a PDF encompasses display. Your RDBMS is not a backup management tool. You can easily back up the database and the filesystem at the same time.

                      – Evan Carroll
                      May 26 '17 at 8:04







                      @ChrisTravers They're not just values: they're files. You can't seek through a value. You can always seek through a file. A value is a piece of data. A file, especially a pdf file, is an input for an external program. A database does not manage the display, a PDF encompasses display. Your RDBMS is not a backup management tool. You can easily back up the database and the filesystem at the same time.

                      – Evan Carroll
                      May 26 '17 at 8:04















                      If you want to seek, lobs in Postgres give you that. Usually it is not what you want in a db however.

                      – Chris Travers
                      May 26 '17 at 8:17





                      If you want to seek, lobs in Postgres give you that. Usually it is not what you want in a db however.

                      – Chris Travers
                      May 26 '17 at 8:17




                      2




                      2





                      So have an accounting system. Want to track invoice pdfs as sent out, and guarantee that these are properly tracked to the transactions for auditing reasons. Why not store them in PostgreSQL. All we need to do is store them, retrieve them, and most importantly back up and restore them with the rest of the data? I.e. they are values for auditing purposes.

                      – Chris Travers
                      May 26 '17 at 8:24





                      So have an accounting system. Want to track invoice pdfs as sent out, and guarantee that these are properly tracked to the transactions for auditing reasons. Why not store them in PostgreSQL. All we need to do is store them, retrieve them, and most importantly back up and restore them with the rest of the data? I.e. they are values for auditing purposes.

                      – Chris Travers
                      May 26 '17 at 8:24




                      1




                      1





                      No reason whatsoever is a dangerous statement and almost always wrong.

                      – Chris Travers
                      May 26 '17 at 8:25





                      No reason whatsoever is a dangerous statement and almost always wrong.

                      – Chris Travers
                      May 26 '17 at 8:25


















                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Database Administrators Stack Exchange!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f36493%2fhow-can-i-store-a-pdf-in-postgresql%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      Liste der Baudenkmale in Friedland (Mecklenburg)

                      Single-Malt-Whisky

                      Czorneboh