5

We maintain our data in a PostgreSQL database, and keep the relevant documentation in the form of Word (TM) files.

Now a peculiar sentence in the description of the project states that the documentation should be stored 'together with the data, in the data base.'

My question: Can I store a Word document in a PostgreSQL database?

It's okay if I have to zip it first, maybe even convert to .pdf, or similar tricks. But it should of cause be possible to retrieve the data out again, and re-create the document we put in.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
Martin
  • 391
  • 1
  • 4
  • 15
  • 1
    You can store this using a binary data type: https://www.postgresql.org/docs/current/static/datatype-binary.html. – Gordon Linoff Sep 19 '16 at 12:31
  • It is not possible to search for text inside a Word doc when it is stored in the database. – Clodoaldo Neto Sep 19 '16 at 18:14
  • 1
    You can store anything you want in PostgreSQL. Whether it's a good idea, on the other hand, is an entirely different question. – Jonathan Hall Nov 03 '20 at 11:38
  • Duplicate of [how to use Blob datatype in Postgres](https://stackoverflow.com/questions/4386030/how-to-use-blob-datatype-in-postgres) – TylerH Nov 03 '20 at 14:34

1 Answers1

14

Yes, you can!

A column of type bytea can hold up to 1 GB of binary data.

Considering the following example:

CREATE TABLE tbl_document
(
    id BIGINT, 
    info TEXT, 
    doc BYTEA, 
    CONSTRAINT pk_tbl_document PRIMARY KEY ( id )
);

Use decode() function to store a document file from its base64 representation:

INSERT INTO tbl_document ( id, info, doc ) VALUES ( 1, 'Personal Resume', decode( 'TG9yZW0gaXBzdW0u', 'base64')::bytea  );

Use encode() function to retrieve back the document in its base64 representation:

SELECT info, encode( doc, 'base64' ) FROM tbl_document WHERE id = 1;

Use pg_read_file() function to read binary files directly into the database:

INSERT INTO tbl_document ( id, info, doc ) VALUES ( 2, 'Daily Report', pg_read_file('report.doc')::bytea );

Hope It Helps!

Lacobus
  • 1,590
  • 12
  • 20