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

Question

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?

@jjanes I want support a query like this SELECT * FROM mytable WHERE subject && '{"journal", "book"}'; I've updated my question accordingly, thanks for the hint — itinance
– itinance, Commented Jan 15, 2024 at 3:12

jjanes · Accepted Answer · 2024-01-15 09:14:48Z

1

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

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

answered Jan 15, 2024 at 9:14

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related