Postgresql: Full text search within lob possible?

Asked 2012-10-29T09:18:43.853

9

We'd like to use PostgreSQL to store documents. As some of them might be up to 2 GB big, we have to use the lob-Datatype, where the large objects are stored in a separate table (pg_largeobject), referenced by an OID, as per the docs on large objects.

For us it's very important that these documents (.pdf, .doc, ...) can be searched. With the built-in PostgreSQL full text search tables and columns can be searched, but is it possible to search the large-objects in the pg_largeobject table as well?

If not, we have to use Oracle.

postgresql search full-text-search

Edit Report

1 Answer

3

There are at least two issues here.

Full-text search doesn't really work on large objects stored as lob or oid-references. You cannot full-text index the contents of pg_largeobject.
Full-text search is an indexing system for text. It cannot index PDF, Microsoft Word documents, or other random binary files. It does not have provision for text-extraction tool callbacks, etc.

You can either:

Create a table that contains text extracted from those files using external tools along with an oid that refers to the file its self, then full-text index that table of extracted text; or
Use a more powerful, full-featured external search system like Solr (based on Lucene) that's designed to cope with varying formats, do its own text extraction, etc.

answered 2012-10-29T09:24:40.670

Your Answer