Skip to content

Instantly share code, notes, and snippets.

@PJUllrich

PJUllrich/example.exs

Created October 22, 2022 11:00
Show Gist options
  • Save PJUllrich/ff1b1cdc07c4e6d3ed87586098b5dcec to your computer and use it in GitHub Desktop.
Save PJUllrich/ff1b1cdc07c4e6d3ed87586098b5dcec to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. PJUllrich created this gist Oct 22, 2022.
    42 changes: 42 additions & 0 deletions example.exs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,42 @@
    ## Migration
    def up do
    create table(:books) do
    add :title, :string
    add :summary, :text
    add :language, :text
    end

    for lang <- ['english', 'spanish', 'italian'] do
    execute """
    CREATE INDEX books_partial_idx_#{lang} ON books USING GIN(to_tsvector('#{lang}', coalesce(title, '') || ' ' || coalesce(summary, '')))
    WHERE language = '#{lang}';
    """
    end
    end

    ## Example query
    def search_partial_indexes(language, search_term) do
    query =
    from(b in Book,
    where: b.language == ^language,
    where:
    fragment(
    "to_tsvector(?, coalesce(title, '') || ' ' || coalesce(summary, '')) @@ to_tsquery(?, ?)",
    ^language,
    ^language,
    ^search_term
    ),
    select: [:title, :summary]
    )

    Repo.explain(:all, query, analyze: true)
    end

    ## Error output
    SELECT b0."title", b0."summary" FROM "books" AS b0 WHERE (b0."language" = $1) AND (to_tsvector($2, coalesce(title, '') || ' ' || coalesce(summary, '')) @@ to_tsquery($3, $4)) ["english", "english", "english", "prince"]
    ↳ :erl_eval.do_apply/7, at: erl_eval.erl:744
    ** (Postgrex.QueryError) type `regconfig` can not be handled by the types module Postgrex.DefaultTypes
    (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:932: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:847: Ecto.Adapters.SQL.execute/6
    (ecto 3.8.4) lib/ecto/repo/queryable.ex:221: Ecto.Repo.Queryable.execute/4
    (ecto 3.8.4) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3