Problem with Ecto fragment and type

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))

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
    |> where([g], contains(g.num_players, ^num_players))
    |> Repo.all

Here is the table:

games_dev=# \d games
                            Table ""
    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?


  • Ecto 2.2.6
  • Elixir 1.5.0
  • Postgres 10.1

Created a minimal reproduction repo.

Test for yourself with:

git clone
cd bigint_bug_repro 
mix deps.get
mix test

You should see output like:

raw query: {"SELECT b0.\"id\", b0.\"num_players\" FROM \"bug_example\" AS b0 WHERE (b0.\"num_players\" @> $1::bigint)",  [1]}

1) test Contains query (BugTest)
   ** (Postgrex.Error) ERROR 42883 (undefined_function): operator does not exist: int4range @> bigint
   code: Bug.test
     (ecto) lib/ecto/adapters/sql.ex:431: Ecto.Adapters.SQL.execute_and_cache/7
     (ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
     (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4
     test/bug_test.exs:5: (test)
1 Like

Okay, I think I found the troublesome commit:

I’ll raise an issue on Ecto.

Edit: Raised at


To follow up on this it is marked as won’t fix for now until someone comes forward with a solution or more people run into this problem.

1 Like

But, other people have…

It makes no sense to convert integer to bigint conceptually either, they are not the same type…

1 Like