CODEWITHSUNDEEP
indexingpostgresql-9.1
Ram
Ram

Reputation: 805

creating gin index in postgreqsql

I am using "PostgreSQL 9.1.5",i am trying to create gin index for the text column.It is showing error message.

CREATE TABLE users (
    first_name text,
    last_name text
)


CREATE INDEX users_search_idx ON users USING gin (first_name,last_name)

I am having column values like this(first_name)

"456fc0470a95c02bb42ef3e35fc81852"

i am trying to query the data like this.

SELECT count(*) FROM users where first_name like '%aeb%'

Upvotes: 7

Views: 20519

Answers (1)

user330315
user330315

Reputation:

If you want to make use of a trigram search, you need to first install the pg_trgm extension:

create extension pg_trgm;

(This needs to be done as the superuser)

Then you need to provide the operator class to be used in the index:

CREATE INDEX users_search_idx 
   ON users USING gin (first_name gin_trgm_ops, last_name gin_trgm_ops);

But a combined index on both columns would not support e.g. where last_name like%aeb%'`. If you need to search on the two column individually, you need two indexes.

Upvotes: 16

Related Questions