I have a custom bit of Ecto code that was working just fine before. I am trying to do a contains query (@>) on an int4range column. The error that I’m getting is:
ERROR 42883 (undefined_function): operator does not exist: int4range @> bigint
Here is the code:
defmodule PG.Queries do
defmacro contains(first, second) do
quote do
# TODO: WHy is :integer becoming bigint?
fragment("? @> ?", unquote(first), type(unquote(second), :integer))
end
end
end
And a minimal example of how it is used:
defmodule Games.DB.GameQuery do
import Ecto.Query
import PG.Queries
alias Games.Repo
def call() do
num_players = 1
query = from g in Games.DB.Game
query
|> where([g], contains(g.num_players, ^num_players))
|> Repo.all
end
end
Here is the table:
games_dev=# \d games
Table "public.games"
Column | Type | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+---------
id | uuid | | not null |
name | text | | |
description | text | | |
num_players | int4range | | |
playing_time | int4range | | |
inserted_at | timestamp without time zone | | not null |
updated_at | timestamp without time zone | | not null |
Here is the query:
SELECT g0."id", g0."description", g0."name", g0."num_players", g0."playing_time",
g0."inserted_at", g0."updated_at" FROM "games" AS g0 WHERE (g0."num_players" @> 1::bigint);
ERROR: operator does not exist: int4range @> bigint
LINE 1: ...ed_at" FROM "games" AS g0 WHERE (g0."num_players" @> 1::bigi...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
If I change the query to use ::integer instead of ::bigint it works fine:
games_dev=# SELECT * FROM "games" AS g0 WHERE (g0."num_players" @> 1::integer);
id | name | description | num_players | playing_time |
inserted_at | updated_at
--------------------------------------+-----------+-------------+-------------+--------------+
----------------------------+----------------------------
5369065d-8a9b-4e39-9b3b-529442e07fd1 | Test game | | [1,3) | [10,21) |
2017-12-06 08:12:04.445278 | 2017-12-06 08:12:04.449007
(1 row)
I am at a loss for why 1 is being cast as 1::bigint instead of 1::integer, does anyone understand what is happening?
Environment:
- Ecto 2.2.6
- Elixir 1.5.0
- Postgres 10.1
