Postgresql: Full text search within lob possible?
full-text-search, postgresql, search
Solution
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.
Problem
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.