Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

What version of chatgpt are you?

  1. User

    What version of chatgpt are you?

  2. Assistant

    I am using the GPT-3 version of ChatGPT.

  3. User

    What is the best way to index postgres column for case insensitive wildcard search, for example ILIKE '%pattern%'

  4. Assistant

    To optimize a PostgreSQL column for case-insensitive wildcard searches using ILIKE '%pattern%', you can create a trigram index. Trigram indexes tokenize text into groups of three letters to efficiently support wildcard search patterns.

    Here's how you can create a trigram index on a column in PostgreSQL:

    -- Create an extension if it's not already enabled
    CREATE EXTENSION IF NOT EXISTS pg_trgm;
    
    -- Create a table with the column you want to index
    CREATE TABLE my_table (
        id serial primary key,
        text_column text
    );
    
    -- Create the trigram index
    CREATE INDEX idx_text_column_trigram ON my_table USING gin (text_column gin_trgm_ops);
    

    Once the trigram index is created, you can perform case-insensitive wildcard searches efficiently using ILIKE:

    SELECT * FROM my_table WHERE text_column ILIKE '%pattern%';
    

    The trigram index will help speed up the performance of such wildcard searches.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.