cancel
Showing results for 
Search instead for 
Did you mean: 

PostgreSQL Index type

Level 2 Adventurer

PostgreSQL Index type

Hi,

 

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 ?

3 REPLIES 3
Highlighted
EDB Team Member

Re: PostgreSQL Index type

Hi @neiljo10,

 

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 : 

 

https://www.postgresql.org/docs/11/textsearch-indexes.html

 

Regards,

Sudhir

Level 2 Adventurer

Re: PostgreSQL Index type

Hi @slonkar 

 

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 ?

Level 3 Adventurer

Re: PostgreSQL Index type

Hi,

 

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:

 

BRIN:

postgres=# create table test_brin (test bytea);

CREATE TABLE

postgres=# create index idx_brin on test_brin using brin (test);

CREATE INDEX

 

GIN:

postgres=# create extension btree_gin;

CREATE EXTENSION

postgres=# create table test_gin (test bytea);

CREATE TABLE

postgres=# create index idx_gin on test_gin using gin(test);

CREATE INDEX

 

GIST:

postgres=# create extension btree_gist;

CREATE EXTENSION

postgres=# create table test_gist (test bytea);

CREATE TABLE

postgres=# create index idx_gist on test_gist using gist(test);

CREATE INDEX

 

 

Hope this answers your question.