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

Phoenix Forum Questions / Help
, ,
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
1 Like
2

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

3 Likes
3

Yes its my mistake. Thank you very much for your help:-)

4

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"]]
}
1 Like
5

SELECT $1::integer AS foo

6

What if I don’t know the type of the params? They could be integers, floats, booleans, strings, or even dates.

7

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.

1 Like
8

So I guess when querying with raw SQL, the best shot is to write a type conversion util and explicitly add types after each placeholder.

9

But it’s a little bit weird that this works:

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

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

so does this:

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

but this fails:

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

What’s the difference between placeholders in SELECT clause versus placeholders in WHERE clause, and the what’s the difference between, well, I don’t know how to name it?

10

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.

1 Like
11

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.