Indexing binary data in PostgreSQL?

Question

I am converting an Oracle 11G DB to PostgreSQL v11 DB.

I am super new to the whole database thing and I have encountered a query using index type of ctxsys.context to create an index.

I have no idea what ctxsys index type is doing as I tried reading oracle documents related to it but got confused.

I wish to know what exactly this indextype does and

Is there any equivalent solution in PostgreSQL ?

Below is the query for all of your references,

CREATE INDEX IDX1 ON TBL1(BLOB Column) indextype is ctxsys.context;
CREATE INDEX IDX2 ON TBL2(BLOB Column) indextype is ctxsys.context;

I am told you can't index bytea columns (which is the equivalent to blob) in Postgres. Can I move only the lob column to a user defined tablespace ?

Answer 1

Oracle text search filter is capable of automatically parsing non-plain text documents, such as MS Word or PDF, while Postgres' text search can only handle plain text documents. You'll have to convert whatever is stored in your BLOB column to plain text before loading those data into a Postgres TEXT column, then build the Postgres text search index on that.