type regconfig can not be handled by the types module Postgrex.DefaultTypes

[asakura]

PostgreSQL 12.2
Postgrex 0.15.3

Can not use text search configurations passed via raw query parameters:

iex(9)> Ecto.Adapters.SQL.query(Repo, "select $1::text::regconfig", ["english"])
[debug] QUERY ERROR db=0.0ms queue=0.3ms idle=9742.4ms
select $1::text::regconfig ["english"]
[error] Postgrex.Protocol (#PID<0.464.0>) disconnected: ** (Postgrex.QueryError) type `regconfig` can not be handled by the types module Postgrex.DefaultTypes
{:error,
 %Postgrex.QueryError{
   message: "type `regconfig` can not be handled by the types module Postgrex.DefaultTypes"
 }}

Same query works from psql:

# select 'english'::text::regconfig;
 regconfig 
-----------
 english
(1 row)

[josevalim]

Correct. Postgrex doesn’t support all possible PG types, especially because they can be provided by extensions. In this case, you have to create your own Modules to handle this. See Postgrex docs. Odds are that someone has already done it, so please search existing packages. :)

[kiddoshainingu]

I'm curious to know if anybody has written an extension for regconfig ever since ?

[PJUllrich]

Just an FYI for the future: In certain cases, you can use a trick to make Postgrex work with regconfig. For example, the following query won't work:

def search_comment_content(language, search_term) do
  query = from(c in Comment, where: fragment("content @@ plainto_tsquery(?, ?)", ^language, ^search_term)
  Repo.all(query)
end

Postgrex doesn't know how to translate the language from a binary into regconfig and you will receive the following error:

[error] Postgrex.Protocol (#PID<0.424.0>) disconnected: ** (Postgrex.QueryError) type `regconfig` can not be handled by the types module Postgrex.DefaultTypes

So, the trick is to type cast your language first into text and then into regconfig in your fragment, like this (note the ?::text::regconfig):

def search_comment_content(language, search_term) do
  query = from(c in Comment, where: fragment("content @@ plainto_tsquery(?::text::regconfig, ?)", ^language, ^search_term)
  Repo.all(query)
end

This way, Postgrex will transmit the language as text and Postgres handles the translation into regconfig. This query now works without using an external extension (which does not exist, yet, it appears).

[greg-rychlewski]

For some context, ^ works because the result is a boolean. But if the result is a type that is unknown to Postgrex the error happens:

select $1::text::regconfig --> errors because postgrex gets regconfig type back from the db

select plainto_tsquery($1::text::regconfig, 'hi') --> errors because postgrex gets tsquery type back from the db

[greg-rychlewski]

For posterity:

I've created a small library that can enable text encoding for any data type in Postgrex: https://github.com/greg-rychlewski/postgrex_text_ext.

This works well with OID types like regconfig where most of the time you want to use the string name instead of the integer.