Building a simple search input with PostgreSQL full-text search features

@charlesdemersOctober 13th, 2021

PostgreSQL provides us with powerful tools to perform full-text search on our data. Today I used them to build a simple search input.

The first step was to add a column to index our data without special characters to support french:

defmodule Library.Repo.Migrations.AddSearchableTitleToBooks do
  use Ecto.Migration

  def up do
    execute("CREATE EXTENSION IF NOT EXISTS unaccent")

    execute("""
    CREATE OR REPLACE FUNCTION normalize_string(varchar)
      RETURNS varchar
      LANGUAGE SQL
      IMMUTABLE
    AS $$
      SELECT lower(regexp_replace(unaccent($1), '[^\\\w\\\-_.@ ]+', '','g'));
    $$;
    """)

    execute("""
      ALTER TABLE books
      ADD COLUMN searchable_title tsvector
      GENERATED ALWAYS AS (
        setweight(to_tsvector('english', normalize_string(coalesce(title,''))), 'A') ||
        setweight(to_tsvector('french', normalize_string(coalesce(title,''))), 'A')
      ) STORED;
    """)
  end

  def down do
    alter(table(:books)) do
      remove(:searchable_title)
    end

    execute("DROP FUNCTION IF EXISTS normalize_string")
    execute("DROP EXTENSION IF EXISTS unaccent")
  end
end

Finally, we can query the data like this:

# We need this because if the query contains one than one word, we need to concatenate them with a `+`
searchable_title = String.replace(title, ~r/\s+/, "+")

Repo.all(
  from(
    Book,
    select_merge: %{
      rank: fragment("ts_rank_cd(searchable_title, to_tsquery(unaccent(?))) as rank", ^searchable_title)
    },
    where: fragment("searchable_title @@ to_tsquery(unaccent(?))", ^searchable_title),
    order_by: [fragment("rank")]
  )
)