Operator error when comparing bigint and character varying values: (Postgrex.Error) ERROR 42883 (undefined_function) operator does not exist: bigint = character varying

Phoenix Forum Questions / Help
1

When running the “create session_occurence” test in test/dbservice_web/controllers/session_occurence_controller_test.exs, I encountered the following error:

  1) test update session_occurence renders session_occurence when data is valid (DbserviceWeb.SessionOccurenceControllerTest)
     test/dbservice_web/controllers/session_occurence_controller_test.exs:67
     ** (Postgrex.Error) ERROR 42883 (undefined_function) operator does not exist: bigint = character varying

         query: SELECT u0."id", u0."address", u0."city", u0."district", u0."email", u0."full_name", u0."gender", u0."phone", u0."pincode", u0."role", u0."state", u0."whatsapp_phone", u0."date_of_birth", u0."country", u0."inserted_at", u0."updated_at", u1."session_occurence_id"::bigint FROM "user" AS u0 INNER JOIN "user_session" AS u1 ON u0."id" = u1."user_id" WHERE (u1."session_occurence_id" = ANY($1)) ORDER BY u1."session_occurence_id"::bigint

         hint: No operator matches the given name and argument types. You might need to add explicit type casts.
     code: put(
     stacktrace:
       (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
       (elixir 1.14.2) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2
       (dbservice 0.1.0) lib/dbservice_web/views/session_occurence_view.ex:26: DbserviceWeb.SessionOccurenceView.render/2
       (phoenix_view 1.1.2) lib/phoenix/view.ex:475: Phoenix.View.render_to_iodata/3
       (phoenix 1.6.10) lib/phoenix/controller.ex:772: Phoenix.Controller.render_and_send/4
       (dbservice 0.1.0) lib/dbservice_web/controllers/session_occurence_controller.ex:1: DbserviceWeb.SessionOccurenceController.action/2       
       (dbservice 0.1.0) lib/dbservice_web/controllers/session_occurence_controller.ex:1: DbserviceWeb.SessionOccurenceController.phoenix_controller_pipeline/2
       (phoenix 1.6.10) lib/phoenix/router.ex:354: Phoenix.Router.__call__/2
       (dbservice 0.1.0) lib/dbservice_web/endpoint.ex:1: DbserviceWeb.Endpoint.plug_builder_call/2
       (dbservice 0.1.0) lib/dbservice_web/endpoint.ex:1: DbserviceWeb.Endpoint.call/2
       (phoenix 1.6.10) lib/phoenix/test/conn_test.ex:225: Phoenix.ConnTest.dispatch/5
       test/dbservice_web/controllers/session_occurence_controller_test.exs:72: (test)

Here is my test code

  describe "create session_occurence" do
    test "renders session_occurence when data is valid", %{conn: conn} do
      conn = post(conn, Routes.session_occurence_path(conn, :create), get_ids_create_attrs())

      %{"id" => id} = json_response(conn, 201)

      conn = get(conn, Routes.session_occurence_path(conn, :show, id))

      assert %{
               "id" => ^id,
               "end_time" => "2022-04-28T14:05:00Z",
               "start_time" => "2022-04-28T14:05:00Z"
             } = json_response(conn, 200)
    end
defp get_ids_create_attrs do
    session_occurence_fixture = session_occurence_fixture()
    session_id = session_occurence_fixture.session_id
    Map.merge(@create_attrs, %{session_id: session_id})
  end

and here is my sessions_fixtures.ex

def session_occurence_fixture(attrs \\ %{}) do
    {:ok, session_occurence} =
      attrs
      |> Enum.into(%{
        end_time: ~U[2022-04-28 14:05:00Z],
        start_time: ~U[2022-04-28 14:05:00Z],
        session_id: get_session_id()
      })
      |> Dbservice.Sessions.create_session_occurence()

    session_occurence
  end
def get_session_id do
    [head | _tail] = Sessions.list_session_occurence()
    session_id = head.session_id
    session_id
  end

and here is my Dbservice.Sessions

 def create_session_occurence(attrs \\ %{}) do
    %SessionOccurence{}
    |> SessionOccurence.changeset(attrs)
    |> Repo.insert()
  end

and here is my session_occurrence.ex

defmodule Dbservice.Sessions.SessionOccurence do
  @moduledoc false

  use Ecto.Schema
  import Ecto.Changeset
  import Dbservice.Utils.Util

  alias Dbservice.Users.User

  schema "session_occurrence" do
    field :end_time, :utc_datetime
    field :start_time, :utc_datetime
    field :session_fk, :id
    field :session_id, :string

    timestamps()

    many_to_many :users, User, join_through: "user_session", on_replace: :delete
  end

  @doc false
  def changeset(session_occurrence, attrs) do
    session_occurrence
    |> cast(attrs, [:session_id, :start_time, :end_time, :session_fk])
    |> validate_required([:session_id])
    |> validate_start_end_date_time
  end

  defp validate_start_end_date_time(changeset) do
    if get_field(changeset, :start_time, :end_time) != nil do
      validate_start_end_datetime(changeset, :start_time, :end_time)
    else
      changeset
    end
  end
end
2

There’s a lot of code in your post, but not this code - hard to figure out what’s wrong without seeing what’s being tried.

The database schema (not the Ecto schemas, the actual schema reported by PG) would be helpful as well.

The ::bigint in the ORDER BY clause suggests that the type of session_occurrence_id possibly isn’t what we’re expecting, I’d start looking there.

3

Hey @al2o3cr In my database session_id present as a string , I guess this is creating problem , correct me if I’m wrong here ? can you help me how I can debug this error ?

4

I’m not sure what’s going on, but the major comparison in the SQL that’s failing is this:

WHERE (u1."session_occurence_id" = ANY($1)) ORDER BY u1."session_occurence_id"::bigint

That suggests a couple possibilities:

  • the list passed into this query in $1 (presumably by code in SessionOccurenceView) doesn’t contain the right type of data

  • the list in $1 is correct, but the column being compared (session_occurence_id) isn’t the right type

To tell these situations apart, you’ll need to show the code from SessionOccurenceView and check your database tables.

5

@al2o3cr Here is my Session_occurence_view.ex

defmodule DbserviceWeb.SessionOccurenceView do
  use DbserviceWeb, :view
  alias DbserviceWeb.SessionOccurenceView
  alias DbserviceWeb.UserView
  alias Dbservice.Repo

  def render("index.json", %{session_occurence: session_occurence}) do
    render_many(session_occurence, SessionOccurenceView, "session_occurence.json")
  end

  def render("show.json", %{session_occurence: session_occurence}) do
    render_one(session_occurence, SessionOccurenceView, "session_occurence_with_users.json")
  end

  def render("session_occurence.json", %{session_occurence: session_occurence}) do
    %{
      id: session_occurence.id,
      start_time: session_occurence.start_time,
      end_time: session_occurence.end_time,
      session_fk: session_occurence.session_fk,
      session_id: session_occurence.session_id
    }
  end

  def render("session_occurence_with_users.json", %{session_occurence: session_occurence}) do
    session_occurence = session_occurence |> Repo.preload(:users)

    %{
      id: session_occurence.id,
      start_time: session_occurence.start_time,
      end_time: session_occurence.end_time,
      session_fk: session_occurence.session_fk,
      session_id: session_occurence.session_id,
      users: render_many(session_occurence.users, UserView, "user.json")
    }
  end
end

also here is migration

defmodule Dbservice.Repo.Migrations.CreateUserSession do
  use Ecto.Migration

  def change do
    create table(:user_session) do
      add :start_time, :utc_datetime
      add :end_time, :utc_datetime
      add :data, :map
      add :user_id, references(:user, on_delete: :nothing)
      add :session_occurence_id, references(:session_occurence, on_delete: :nothing)

      timestamps()
    end

    create index(:user_session, [:user_id])
    create index(:user_session, [:session_occurence_id])
  end
end

I have a user_session table here there is session_occurence_id , after clicking on the this Id I get the Id of session_occurence table entry

databse

1 Like
6

Some things we can extract from this new information:

  • the failing line is session_occurence = session_occurence |> Repo.preload(:users), which means the data in $1 is coming from the DB via Ecto’s preload code

  • the schema of user_session tells us we were looking at the wrong part of the query - the failure is on trying to join user.id against user_session.user_id despite user_id being a varchar(255) and id (presumably) being a bigint.

1 Like
7

@al2o3cr yeah I think you are correct, What needs to change here ? so that the error will removed

8

I’m not clear how the user_id column ended up with that type; the migration you posted should have correctly picked bigint since there aren’t any other options passed to references.

If you don’t care about the data in your DB, you could drop it and recreate it and see what happens.

If you do care about the data (because it’s in production, for instance) you’ll have to work more carefully to migrate things. That may be easy (just let PG convert them all in one big transaction) or very hard (if there are LOTS, or something’s been writing non-integer junk in user_id) depending the specifics of what’s in the table.

2 Likes
9

@al2o3cr Yeah I tried to drop and recreate all the table but nothing happens , same error occurs.

10

@al2o3cr yes you are right , the failing line is session_occurence = session_occurence |> Repo.preload(:users) the error is removed when I remove this line of code but I don’t know how to remove the mismatch data type error without removing this line ?

12

You need to fix the column types. Your previous post mentioned that you dropped and recreated the table, but did you do it with MIX_ENV=test?

Beyond that I don’t have any immediate insight; the code you’ve posted looks like it should work and create the columns with the right type.

1 Like
13

@al2o3cr Yes
here is my dev.exs

config :dbservice, Dbservice.Repo,
  username: "postgres",
  password: "postgres",
  hostname: "localhost",
  database: "dbservice_test",

then I do mix ecto.migrate
then mix run priv/repo/seeds.exs

but still getting same error
also user_id should be string , its not right to change it to bigint
I have to find a another way to handle this .

14

Setting the database to dbservice_test in dev.exs is not quite what I meant. A common idiom when starting out a new Ecto project is “tear everything down and start over”; for the test environment it’s this sequence of commands:

MIX_ENV=test mix ecto.drop
MIX_ENV=test mix ecto.create
MIX_ENV=test mix ecto.migrate

(There are shorthands for this, and your mix.exs may even define a single name for it - but this is clearer for now)

This drops the DB, creates it, and runs the migrations. It will ensure the DB matches the structures defined in the migrations.

If you aren’t doing the drop / create steps above, running ecto.migrate after changing migrations will not rerun migrations that have been changed.

Can you explain this requirement further? It is not expressed in your migration, which will create a bigint user_id column:

add :user_id, references(:user, on_delete: :nothing)

If this column needs to be a string, there should be a type option passed to references.

BUT if user_id is a string, then the id column on user needs to be a string too - Postgres only allows foreign keys between columns that can be implicitly typecast to each other, and bigint and varchar can not.

A final big question: where did that string user_id column come from in the first place? Two ways I can think of:

  • manual editing of the schema via a GUI tool. Don’t do this.

  • the CreateUserSession migration used to have different contents and was edited after it had run. There are ways to do this safely (ecto.rollback the migration, edit it, then ecto.migrate forward again) but you’ll need to be careful.

2 Likes
15

@al2o3cr after doing this way add :user_id, references(:user, type: :string, on_delete: :nothing) and runs again ecto.migrate the following error occurs :

** (Postgrex.Error) ERROR 42804 (datatype_mismatch) foreign key constraint "user_session_user_id_fkey" cannot be implemented

Key columns "user_id" and "id" are of incompatible types: character varying and bigint.
    (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:932: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.14.2) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:1024: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.8.3) lib/ecto/migration/runner.ex:352: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql 3.8.3) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir 1.14.2) lib/enum.ex:2468: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql 3.8.3) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
    (ecto_sql 3.8.3) lib/ecto/migration/runner.ex:289: Ecto.Migration.Runner.perform_operation/3
    (stdlib 4.0.1) timer.erl:235: :timer.tc/1
    (ecto_sql 3.8.3) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/8
    (ecto_sql 3.8.3) lib/ecto/migrator.ex:348: Ecto.Migrator.attempt/8
    (ecto_sql 3.8.3) lib/ecto/migrator.ex:263: anonymous fn/5 in Ecto.Migrator.do_up/5
    (ecto_sql 3.8.3) lib/ecto/migrator.ex:319: anonymous fn/6 in Ecto.Migrator.async_migrate_maybe_in_transaction/7
    (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:1222: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection 2.4.3) lib/db_connection.ex:1611: DBConnection.run_transaction/4
    (ecto_sql 3.8.3) lib/ecto/migrator.ex:337: Ecto.Migrator.run_maybe_in_transaction/5
    (elixir 1.14.2) lib/task/supervised.ex:89: Task.Supervised.invoke_mfa/2
    (elixir 1.14.2) lib/task/supervised.ex:34: Task.Supervised.reply/4

Here is the code to add user_id :

defmodule Dbservice.Repo.Migrations.AddUserIdInUserSession do
  use Ecto.Migration

  def change do
    alter table("user_session") do
      add :user_id, :string
    end
  end
end

Here is the user_session_view

defmodule DbserviceWeb.UserSessionView do
  use DbserviceWeb, :view
  alias DbserviceWeb.UserSessionView

  def render("index.json", %{user_session: user_session}) do
    render_many(user_session, UserSessionView, "user_session.json")
  end

  def render("show.json", %{user_session: user_session}) do
    render_one(user_session, UserSessionView, "user_session.json")
  end

  def render("user_session.json", %{user_session: user_session}) do
    %{
      id: user_session.id,
      start_time: user_session.start_time,
      end_time: user_session.end_time,
      session_occurrence_id: user_session.session_occurrence_id,
      data: user_session.data,
      is_user_valid: user_session.is_user_valid,
      user_id: user_session.user_id
    }
  end
end

From these code can you help to get any insight how can I resolve my error ?

16

user_session.user_id and user.id need to be compatible types to have a foreign key constraint in Postgres, and varchar and bigint are not compatible.

Why do you want user_session.user_id to be a string?

1 Like
17

@al2o3cr because user_id is something like this 71b5aabfe6043ad33f0022c so that’s why it should be type String not integer. right ?

18

@al2o3cr also my user_id is not a foreign key

session_occurence
session_occurence1489×609 41.2 KB

Is my test code properly written?

19

If the IDs are not actually 23 hexadecimal digits but 32 instead then it’s likely they are UUIDs and Ecto supports those just fine.

Can you copy-paste a few IDs without editing or cutting them?

20

@dimitarvp Here are few ID’s
71b5aa6043ad33f0022c
544cc0a730ff59c2f577
d0988ebb15ceb9cf3cab
9585e6000e1f580a1595
c72ad6b937e477a92b35

21

OK, these ones are 19 digits. Weird. Why are these IDs and the previous ID you posted not of the same length.

But outside of that and as @al2o3cr said, when you make links in the DB, both sides of the link must use the same ID type – either Postgres biging or, if you really insist on strings so much, you’ll have to introduce migration to change the other ID column to strings as well (though I am not sure then Ecto can work with it, haven’t tried).