V
V
Vladislav2019-05-27 15:46:27
PostgreSQL
Vladislav, 2019-05-27 15:46:27

What is the correct way to use pg_trgm with FTS Pgsql + Laravel?

Hello. Implementing FTS using Pgsql. I am writing a project in Larvel.
Actually, I implemented the search by adding a column to the table and assigning an index (GIN).
Then after scout:importthe table was filled with indexes. Searching them works great, but I can't figure out how to do a substring search. I read that pg_trgm is used for this, but how to fasten it correctly?
Let's say we have a table

Schema::create('region', function (Blueprint $table) {
            $table->integer('id', true);
            $table->string('label_region', 40);
        });


        DB::statement('CREATE EXTENSION IF NOT EXISTS pg_trgm');
        DB::statement('ALTER TABLE "region" ADD searchable tsvector NULL');
        DB::statement('CREATE INDEX "region_searchable_index" ON "region" USING GIN (searchable)');
        DB::statement('CREATE INDEX label_region_trgm_index ON region USING gin(label_region gin_trgm_ops);');

And how to write a query further so that the substring still works? Before this, I searched through::search

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question