david234

Postgrex ** (DBConnection.EncodeError) Postgrex expected a binary, got 1

Schema File:

defmodule Logistics.Users.User do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "users_info" do
    field :email, :string
    field :name, :string
    field :password, :string
    field :permission_groups, {:array, :string }, default: []
    field :phone, :string

    field :status, :integer, default: 1
    field :parent_id, :binary_id

    timestamps()
  end

  @doc false
  def changeset(user, attrs) do
    user
    |> cast(attrs, [:name, :password, :email, :status, :permission_groups, :phone, :parent_id])
    |> validate_required([:name, :password, :email, :status, :permission_groups, :parent_id])
    |> unique_constraint(:email)
  end

  @doc false
  def add_user_changeset(user, attrs) do
    user
      |> cast(attrs, [:name, :password, :email, :status, :parent_id, :permission_groups, :phone])
      |> validate_required([:name, :password, :email, :parent_id, :permission_groups])
      |> unique_constraint(:email)
  end

end

Migration File :

defmodule Logistics.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    create table(:users_info, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :name, :string
      add :password, :string
      add :email, :string
      add :status, :string
      add :permission_groups, {:array, :string}, default: []
      add :phone, :string
      add :parent_id, references(:admins, on_delete: :nothing, type: :binary_id)

      timestamps()
    end

    create unique_index(:users_info, [:email])
    create index(:users_info, [:parent_id])
  end
end

Insert Code:

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

Terminal output:


[debug] QUERY ERROR db=83.6ms queue=75.4ms
INSERT INTO "users_info" ("email","name","parent_id","password","permission_groups","status","inserted_at","updated_at","id") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9) ["karthijaya123@gmail.com", "David", <<207, 113, 150, 134, 183, 228, 76, 96, 191, 94, 131, 86, 40, 231, 35, 163>>, "123456", ["admin_for_locations", "admin_for_products"], 1, ~N[2020-01-02 12:03:14], ~N[2020-01-02 12:03:14], <<229, 190, 241, 115, 65, 251, 78, 123, 131, 149, 30, 115, 68, 61, 125, 3>>]
[info] Sent 500 in 388ms
[error] #PID<0.644.0> running LogisticsWeb.Endpoint (connection #PID<0.640.0>, stream id 2) terminated
Server: 165.22.212.1:6004 (http)
Request: POST /admin/user/add
** (exit) an exception was raised:
    ** (DBConnection.EncodeError) Postgrex expected a binary, got 1. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
        (postgrex) lib/postgrex/type_module.ex:897: Postgrex.DefaultTypes.encode_params/3
        (postgrex) lib/postgrex/query.ex:75: DBConnection.Query.Postgrex.Query.encode/3
        (db_connection) lib/db_connection.ex:1148: DBConnection.encode/5
        (db_connection) lib/db_connection.ex:1246: DBConnection.run_prepare_execute/5
        (db_connection) lib/db_connection.ex:1342: DBConnection.run/6
        (db_connection) lib/db_connection.ex:540: DBConnection.parsed_prepare_execute/5
        (db_connection) lib/db_connection.ex:533: DBConnection.prepare_execute/4
        (postgrex) lib/postgrex.ex:198: Postgrex.query/4
        (ecto_sql) lib/ecto/adapters/sql.ex:666: Ecto.Adapters.SQL.struct/10
        (ecto) lib/ecto/repo/schema.ex:651: Ecto.Repo.Schema.apply/4
        (ecto) lib/ecto/repo/schema.ex:262: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
        (logistics) lib/logistics_web/controllers/admin/user_controller.ex:107: LogisticsWeb.Admin.UserController.create/2
        (logistics) lib/logistics_web/controllers/admin/user_controller.ex:1: LogisticsWeb.Admin.UserController.action/2
        (logistics) lib/logistics_web/controllers/admin/user_controller.ex:1: LogisticsWeb.Admin.UserController.phoenix_controller_pipeline/2
        (phoenix) lib/phoenix/router.ex:288: Phoenix.Router.__call__/2
        (logistics) lib/logistics_web/endpoint.ex:1: LogisticsWeb.Endpoint.plug_builder_call/2
        (logistics) lib/plug/debugger.ex:122: LogisticsWeb.Endpoint."call (overridable 3)"/2
        (logistics) lib/logistics_web/endpoint.ex:1: LogisticsWeb.Endpoint.call/2
        (phoenix) lib/phoenix/endpoint/cowboy2_handler.ex:42: Phoenix.Endpoint.Cowboy2Handler.init/4
        (cowboy) /srv/logistics/deps/cowboy/src/cowboy_handler.erl:41: :cowboy_handler.execute/2

10 comments

/phoenix #ecto #postgrex

1 7676 10

2021-11-09 08:56:39 UTC

Marked As Solved

LostKobrakai

Your :status types are mismatching. You schema types it as :integer and your db/migration as :string.

Post #2

Also Liked

Aetherus

Recently I found a similar problem:

iex> sql = "SELECT $1 AS foo"
iex> params = [1]
iex> Repo.query!(sql, params)

And the same error was raised while I was expecting something like

%Postgrex.Result{
  columns: ["foo"],
  rows: [[1]]
}

So, how can I make this work?

By the way, this works:

iex> sql = "SELECT $1 AS foo"
iex> params = ["bar"]
iex> Repo.query!(sql, params)

%Postgrex.Result{
  columns: ["foo"],
  rows: [["bar"]]
}

Post #4

LostKobrakai

Afaik you need to know types in advance, as you can see by the error. The postgres documentation seems to suggest that inferring the type is possible, but may fail to happen, which is likely the reason for postgrex to be explicit about it.

Post #7

LostKobrakai

Prepared statements can take parameters: values that are substituted into the statement when it is executed. When creating the prepared statement, refer to parameters by position, using $1, $2, etc. A corresponding list of parameter data types can optionally be specified. When a parameter’s data type is not specified or is declared as unknown, the type is inferred from the context in which the parameter is used (if possible).

Source

It’s easy to forget that using parameters is different from having data directly in the sql query. What might work when values are interpolated into the sql, might not work when using parameters, because postgres has not enough information to work with.

In your examples in both initial cases postgres knows the type of one side of the comparison. In the last one there’s essentially no type information available.

Post #10

Last Post!

Aetherus

OK, I guess in the cases of SELECT 1 AS foo WHERE 1 = $1 and SELECT 1 AS foo WHERE $1 = 1, Postgrex has enough information to guess the type of the placeholder (same as the type on the other side of =).

But in the other cases, Postgrex has no clue what type the placeholders are, so it assumes they should be text.

Post #11