CODEWITHSUNDEEP
postgresql
itinance
itinance

Reputation: 12418

How to add an Index on an VarChar-Array column in Postgres?

I have a table with an Array column of varchar(256)-values:

ALTER TABLE "my_table" ADD "subject" VARCHAR(256)[] NULL

And I want to query this field like this:

SELECT * FROM mytable WHERE subject && '{"journal", "book"}';

Since I want to query on values in that particular array, I need an index for performance reasons. However, every attempt failed due to missing storage classes or unsupported column type.

I have tried:

CREATE INDEX idx_daily_subject on "daily" USING GIN ("subject" gin_trgm_ops)
CREATE INDEX idx_daily_subject on "daily" USING GIN (to_tsvector('english', subject))

But then I receive the following error messages:

ERROR: operator class "gin_trgm_ops" does not accept data type character varying[]

successive

ERROR: function to_tsvector(unknown, character varying[]) does not exist

I found a workaround here: Postgres - Indexing long character varying array type column using GIN? but this makes any ORM mapper that I use obsolete (TypeORm in my case).

I have seen examples that for integer-arrays, there is a storage class available gin__int_ops. I couldn't find a suitable thing for text or varchar.

Is there anything that helps me?

Upvotes: 0

Views: 531

Answers (1)

jjanes
jjanes

Reputation: 44305

There is a default operator class for GIN for varchar[] which supports &&.

CREATE INDEX idx_daily_subject on "daily" USING GIN ("subject")

Upvotes: 0

Related Questions