Skip to content

Instantly share code, notes, and snippets.

@PJUllrich
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.
Example Error when using tsvector with regconfig in Ecto
## 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment