Database migration Oracle to PostgreSQL, domain indexes

Question

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 .

• user1822: What is a domain index? What does it do in Oracle? Are you talking about indexes on user-defined domains?
• user1822: For what is this index? What type of query does it improve? Can't you just use create index on usr_account(email)?
• ypercubeᵀᴹ: @Mojib it would better if you provided a complete example (how you created the INDEXTYPE and how it is used, what performance gain you get, etc). Otherwise, only Oracle experts will knwo what you are talking about (but probably have no idea how that can be done in Postgres) and Postgres experts may know how to do it but no idea what you are talking about. You'll be just gambling that an expert in both DBMS will stumble on the question ;)
• ypercubeᵀᴹ: For the actual problem, I guess someone that has knowledge of Postgres extensibility (types, operators, indexes) could answer. Read this in the meantime: Postgres docs: Chapter 37. Extending SQL
• Andriy M: You should realise that it's not very common to be an expert both in Oracle and in PostgreSQL. Presumably you want help from PostgreSQL experts. But they may not know Oracle very well, so you need to help them a little and explain what this kind of index is and how it works in Oracle. That way a PostgreSQL expert will be able to come up with a proper alternative for you.

Answer 1

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.

• user1822: Even without that index, the queries will need to be changed. The FTS query syntax for Oracle is very different to that of Postgres - independently of any index