bigint versus integer error in Ecto with Legacy database

Question

I'm in the following situation: There's a PostgreSQL legacy database from a Rails application I need to interact with.

Phoenix is using Repo with a schema to connect to that database, and so far it seems to work OK, I can retrieve elements and perform some basic queries with the expected results.

Now, I started playing with some advance queries, and I'm getting some issues that I don't how to solve. For example:

from(is in acc, or_where: ^current_user.id in [is.transcriber_id, is.radiologist_id, is.validator_id])

returns the following error

** (Postgrex.Error) ERROR 42P08 (ambiguous_parameter): inconsistent types deduced for parameter $2

bigint versus integer
    (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

After some research, I discovered that ActiveRecord in Rails, stores integers as bigints but I'm not sure Postgrex or Ecto (I really don't who is in charge of this) is interpreting correctly the integer current_user.id I'm using in the query.

I have the feeling that the expression in [is.transcriber_id, is.radiologist_id, is.validator_id] is a bit greedy and maybe I should go with fragments.

UPDATE:

is stands for ImagingStudy. The Rails migration of this model is as follows:

class CreateImagingStudies < ActiveRecord::Migration[5.2]
  def change
    create_table :imaging_studies do |t|
      t.string :status_name
      t.bigint :validator_id
      t.bigint :radiologist_id
      t.bigint :transcriber_id

      t.timestamps
    end
  end
end

The Phoenix schema for manipulating the table corresponding to the imaging_studies is as follows

defmodule Worklist.Studies.ImagingStudy do
  use Ecto.Schema

  schema "imaging_studies" do
    field :transcriber_id, :integer
    field :radiologist_id, :integer
    field :validator_id, :integer

    timestamps(inserted_at: :created_at, updated_at: :updated_at)
  end

There's no schema for users, since they are managed in another service. Consider the current_user a map with an integer field named id.

Could you please show schemas and migrations for both users and is (whatever is is.) They should be declared as described here. — Aleksei Matiushkin
@AlekseiMatiushkin information added. Thanks! Remember that the legacy Rails application was the one in charge of the migrations, Phoenix can only read from the database. — Sebastialonso
Would not type(^current_user.id, :integer) instead of ^current_user.id help (see Ecto.Query.API.type/2)? — Aleksei Matiushkin
Nope, Ecto’s :integer is explicitly mapped to Postgres’ :bigint. See e.g. here. — Aleksei Matiushkin
Wow, wow, take it easy, you are not and let’s see if it indeed works :) — Aleksei Matiushkin

Aleksei Matiushkin Aleksei Matiushkin · Accepted Answer · 2020-02-07T15:02:20

Ecto voluntarily explicitly maps :integer type to Postgres’ :bigint.

That said, by using Ecto.Query.API.type/2 one might explicitly cast the parameter to :bigint to help ecto with proper type mapping:

from(
  is in acc,
  or_where:
    type(^current_user.id, :integer) in [
      is.transcriber_id, is.radiologist_id, is.validator_id
    ]
)

bigint versus integer error in Ecto with Legacy database

1 Answers