Can I store a Word document in a PostgreSQL database? [closed]

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.

Answer

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!