Why error occurred while creating GIN index?

Question

I've created a table to investigate text searching and using GIST and GIN indexes:

CREATE TABLE test
(
  id serial NOT NULL,
  the_text text,
  CONSTRAINT test_pkey PRIMARY KEY (id)
)

And added some random values:

insert into test values (generate_series(1,100000), md5(random()::text))

And wanted to create GIN index:

create index on test using gin(the_text);

But I had an error:

ERROR: data type text has no default operator class for access method "gist"

Could you help me with that?

Answer 1

You need to enable :btree_gin or :btree_gist as well, since it's some sort of dependency for CREATE INDEX to work. The following worked for me and fixed the message ERROR: data type text has no default operator class for access method "gin"

CREATE EXTENSION pg_trgm;
CREATE EXTENSION btree_gin;
CREATE INDEX index_email_gin ON users USING GIN (email);

Answer 2

For full text search use:

CREATE INDEX test_gin_idx ON test USING gin (to_tsvector('english', the_text));

For trigram search you can use pg_trgm extension

CREATE EXTENSION pg_trgm;
CREATE INDEX test_the_text_gin_idx ON test USING GIN (the_text gin_trgm_ops);