CODEWITHSUNDEEP
databasepostgresqlindexing
MegaBomber
MegaBomber

Reputation: 395

Which indexes should I use for inline strings?

I storing some strings like: Barcelona, Real Madrid, Athletico. My program need to search full match of this,

For example:

Barcelona    -> true
Real Madrid  -> true
Real M       -> false
Athletic     -> false

I think to store it inline in text/varchar field, like (just rude example):

|Barcelona|Real Madrid|Athletico| and then just make LIKE %|Real Madrid|%

I don't want to use any heavy things like trigram, just inline string, but which indexing system should I use?

Thx

Upvotes: 1

Views: 114

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

In your case you can use Arrays type for store your data and use ANY keyword in WHERE statement:

CREATE TABLE games (
    teams   text[]
);

INSERT INTO games (teams) 
VALUES ({'Real Madrid','Maccabi Haifa', 'Manchester United'})

SELECT * FROM table WHERE 'Real Madrid'= ANY (teams);

For improve performance GIN Index can be used with Array field:

    CREATE INDEX ON games USING gin(teams);

Upvotes: 1

Related Questions