How to set id in an insert query

Questions / Help
, ,
1

Hi. I’m having a table User. I wish to seed my first user which should be an admin, and I want the admin to have the id=1. So I run something like this: MyApp.Repo.insert!(%User{id: 1, name: admin}). It works well… Until i wish to create a user from my application web interface. It throws this error MyApp.Endpoint (connection #PID<0.477.0>, stream id 1) terminated. From my understanding, the insertion of the seed does not modify the autogenerated id of Posgres, since the first user I’m trying to insert is still trying to have the id=1, instead of id=2 and so on.
Here is a link of the issue related to Postgres https://stackoverflow.com/questions/9108833/postgres-autoincrement-not-updated-on-explicit-id-inserts
How can I solve this issue with Ecto, still having my id as a serial?

2

Could you show some code? Because it doesn’t seem like Ecto error, or at least this single error message doesn’t say so.

3

which part of the code do you wish to see please? the code of the seed is in the post, and the inset in the context is a normal changeset inserted. %User |> User.changeset(attrs) |> Repo.insert() the attrs coming from a web interface.

4

Please always include the full stack trace and error message. Show the value of the params as well, and actual code snippets, not summaries.

2 Likes
5

The StackOverflow post contains a solution for this issue - you need to issue the appropriate setval command to update the ID sequence. You’ll probably need to use something like Ecto.Adapters.SQL.query/4.

BUT

I’d also suggest you think hard about why the user’s ID is critical; it’s generally a Bad Thing to depend on specific values of primary keys…

2 Likes