How to create pg_trgm compound indexes with date columns

1 vote

1 answer

2780 views

                              SELECT col1, max(date1) as max_date
    FROM table
    WHERE col1 ILIKE 'name'
    GROUP BY col1

TYPES: Here col1 is varchar and date1 is timestamp with time zone data type. So created extension CREATE EXTENSION pg_trgm

Then tried the following indexes and got the errors: 

1: Issue: ERROR:  operator class "gin_trgm_ops" does not accept data type timestamp with time zone

    CREATE INDEX CONCURRENTLY trgm_table_col1_date_index
    ON table 
    USING gin (provider_id, date1 gin_trgm_ops);

2: Issue: ERROR:  operator class "text_pattern_ops" does not exist for access method "gin"

     CREATE INDEX CONCURRENTLY trgm_table_col1_date_index
        ON table 
        USING gin (provider_id, date1 text_pattern_ops);

How can I create an index for the above query for faster execution? Any help will be really appreciated!

EDIT:

Asked by Atihska (129 rep)

Jan 23, 2018, 07:30 PM
Last activity: Jan 23, 2018, 09:51 PM

How to create pg_trgm compound indexes with date columns

Related Questions