How to insert a binary UUID?

Questions / Help
,
1

I have troubles figuring out how to insert uuid into postgres while performing a replace_all. I either get this error: “Problem is that I either get this erro errors: Postgrex expected a binary of 16 bytes, got “83b59a3a-e631-4416-9fec-53cc0c149d3d”” or “(Ecto.ChangeError) value <<131, 181, 154, 58, 230, 49, 68, 22, 159, 236, 83, 204, 12, 20, 157, 61>> for Redfrost.Models.Damage.aggregate_id in insert_all does not match type :binary_id” if I try to do Ecto.UUID.dump!(…) before inserting

It seems like my model and my schema are not aligned but I do not know how to remedy it. What should I do?

(for simplifications I have removed a lot of fields)

Here is my model:

defmodule Redfrost.Models.Damage do
  use Ecto.Schema

  @primary_key false
  schema "damages" do
    field :aggregate_id, :binary_id
    field :company_aggregate_id, :binary_id
  end
end

Migration:

defmodule Redfrost.Repo.Migrations.ItemDamageReport do
  use Ecto.Migration

  def change do
    create index(:product_group_tree_nodes, [:parent_aggregate_id])

    create table(:damages, primary_key: false) do
      add :aggregate_id, :uuid, primary_key: true, null: false
      add :company_aggregate_id, :uuid
    end
  end
end

query:

def move_damages(query) do
    damage_query =
      query
      |> ProjectionsCentral.Persistence.Repo.stream(timeout: :infinity)
      |> Stream.chunk_every(@batch_size)
      |> Stream.each(fn damages_batch ->
        {damage_data, damage_tags} =
          damages_batch
          |> Enum.map(fn damage ->

            %{
               aggregate_id: damage.aggregate_id,
               company_aggregate_id: damage.company_aggregate_id,
             }
          end)

        Repo.insert_all(
          Redfrost.Models.Damage,
          # "damages",
          damage_data,
          on_conflict: :replace_all,
          conflict_target: [:aggregate_id]
        )


    ProjectionsCentral.Persistence.Repo.transaction(fn -> Stream.run(damage_query) end,
      timeout: :infinity
    )
    {:ok, "Damages data migration completed"}
  end
2

Use Ecto.UUID as type on the schema over :binary_id

1 Like
3

Solved it! Turns out the error occured in another place then I thought and that changing the cast simply created a type conlfict in another place not in the excerpt I posted.

4

Tried it but I still get the this error:

** (DBConnection.EncodeError) Postgrex expected a binary of 16 bytes, got "83b59a3a-e631-4416-9fec-53cc0c149d3d". Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
    (postgrex 0.20.0) lib/postgrex/type_module.ex:1084: Postgrex.DefaultTypes.encode_list/3
    (postgrex 0.20.0) lib/postgrex/extensions/array.ex:88: Postgrex.Extensions.Array.encode/4
    (postgrex 0.20.0) lib/postgrex/extensions/array.ex:58: Postgrex.Extensions.Array.encode/3
    (postgrex 0.20.0) lib/postgrex/type_module.ex:1084: Postgrex.DefaultTypes.encode_params/3
    (postgrex 0.20.0) lib/postgrex/query.ex:75: DBConnection.Query.Postgrex.Query.encode/3
    (db_connection 2.7.0) lib/db_connection.ex:1449: DBConnection.encode/5
    (db_connection 2.7.0) lib/db_connection.ex:1549: DBConnection.run_prepare_execute/5
    iex:2: (file)
5

:waving_hand:

Try “dumping” the ids to their binary format

%{
  aggregate_id: Ecto.UUID.dump!(damage.aggregate_id),
  company_aggregate_id: Ecto.UUID.dump!(damage.company_aggregate_id),
}

Oops, sorry. I didn’t read your original post :slight_smile: