Ecto schemaless insert_all throwing error on data type missmatch

Hi! Just stumbled upon this error today and I’m not sure I understand why this is happening - besides, of course, the obvious problem Postgrex is reporting:

Postgrex expected a binary of 16 bytes, got “4c67a49e-fd03-4c1a-9679-713e952ecf73”. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

I’m trying to insert a row in a table that has the correct type and is properly migrated:

INSERT INTO "organization_members" ("organization_id","role","user_id") VALUES ($1,$2,$3) [1, "admin", "4c67a49e-fd03-4c1a-9679-713e952ecf73"]

And the migration is:

create table(:organizations_members, primary_key: false) do
  add :organization_id, references(:organizations), primary_key: true
  add :user_id, references(:users, type: :uuid), primary_key: true
  add :role, OrganizationMemberRole.type(), default: "reader"

create index(:organizations_members, [:organization_id, :user_id])

And I’m inserting it like this:

insert_membership = fn repo, %{organization: organization} ->
  membership = %{user_id:, organization_id:, role: "admin"}
  repo.insert_all("organizations_members", [membership])
|> Ecto.Multi.insert(:organization, Organization.changeset(%Organization{}, attrs))
|>, insert_membership)
|> Repo.transaction()
|> case do
  {:ok, %{organization: organization}} -> {:ok, organization}
  {:error, :organization_id, changeset, _} -> {:error, changeset}
  {:error, :members, _, _} -> :error

Interestingly enough, if I insert it by hand I don’t have a problem at all, so I guess something is happening when Postgrex is trying to return the query results. Also, the console outputs:

[debug] Query OK db=0.7ms`
rollback []

I think I’ve read somewhere that it would be possible to specify the data types for schemaless operations (or changesets) I don’t remember exactly. So, if someone has a fresh memory on the subject I’d really appreciate the help.

  • can you have multiple PKs in Postgres?
  • Do you need to specify the type of the FKs in user?

Hi! @cenotaph! Those are not multiple PKs! It generates a composite PK with the two specified fields.
Also, this table is not backed by any schema, hence (I guess) the problem.

But to better respond to that: Yes, if I had a schema I’d have to specify the type in the relationship. But, the only relationship I care about is defined in the “organization” schema using:
many_to_many :members, User, join_through: "organizations_members".

I did not try to use the :join_keys option in the many_to_many because the error happens while inserting, not retrieving the relationship (don’t know if Ecto cares about it at this point).

Ah checked your post on mobile first and missed the important part.

When you retrieved your user through Ecto it did it’s casting and all the magic.

Your is a string and not a binary (for postgres) since you are entering data without ecto casting in the insert_all method it generates that error. The following should help you.

insert_membership = fn repo, %{organization: organization} ->
  membership = %{user_id: Ecto.UUID.dump!(, organization_id:, role: "admin"}
  repo.insert_all("organizations_members", [membership])

Converts a string representing a UUID into a binary.


Yep, this seems about right… I always forget that if you’re not using a changeset to sanitize data you’re basically on your own with Ecto. What caught me by surprise though is that Postgrex wouldn’t just dump it or pass the input down directly to Postgres.

Thanks for the help @cenotaph. Cheers!

1 Like