Oracle to PostgreSQL Migration – Handling Domain Indexes

oraclepostgresql

I am using aws schema conversion tool for migration database oracle to PostgreSQL. I found an error which is not support in PostgreSQL. PostgreSQL doesn't support domain indexes. anyone know what is the solution of domain index in PostgreSQL?

CREATE INDEX CTX_IDX_UFA_EMAIL 
  ON USR_ACCOUNT(EMAIL) 
  INDEXTYPE IS CTXSYS.CTXCAT;

When I create this type of index, a table and a trigger are automatically created in the database. My question is PostgreSQL does not support this type of index. What is the alternative way to convert this type of index in PostgreSQL database that work like Oracle database?

Oracle docs: Using Extensible Indexing.

And more about the "Context" CTXCAT index type.

Best Answer

INDEXTYPE IS CTXSYS.CTXCAT is Oracle's way of defining a "full text index" on a column -- as opposed to an ordinary B-tree.

The good news is that Postgres also has full text indexes, and these are documented extensively at https://www.postgresql.org/docs/current/static/textsearch.html

The basic syntax for creating a similar index in Postgres is

create index CTX_IDX_UFA_EMAIL
on USR_ACCOUNT
using gin (to_tsvector('english', EMAIL));

The bad news for you is that substituting this type of index in the database will mean you also need to change the queries on this column/table. This may or may not also have implications for the user interface -- it could be that end users are currently able to enter search terms that are understood by the Oracle full text index searching implementation.

Related Question