I am moving my DB from Oracle to Postgres.
In one of the query I encountered that it is using ctxsys indextype.
I want to know what ctxsys actually is and is there any solutions in Postgres ?
I searched for ctxsys index types on Oracle, it seems they are used for the full text search.
In Postgres, you have two index types (GIN and GiST) which are available in the postgres. Please refer below link for the reference :
We are creating an index for a BYTEA column.
When asked the same question on stack exchange, one person emphasized on the fact that an LOB column cannot be indexed.
Is it true ?
For bytea column we can create brin, btree index.
For using gin, gist index we have to create btree_gin and btree_gist contrib module.
Please find below test cases:
postgres=# create table test_brin (test bytea);
postgres=# create index idx_brin on test_brin using brin (test);
postgres=# create extension btree_gin;
postgres=# create table test_gin (test bytea);
postgres=# create index idx_gin on test_gin using gin(test);
postgres=# create extension btree_gist;
postgres=# create table test_gist (test bytea);
postgres=# create index idx_gist on test_gist using gist(test);
Hope this answers your question.