I have a quite a interesting problem regarding table primary key when inserting new rows to Postgres DB using Hasura. In a short summary this is what happened: There was old backend for the app that I am currently developing which used MySql database and my task was to move all of data to new Hasura/Postgress DB, so i wrote scripts that move that data and it went fine. Script inserted all of the users data including their primary keys because of user relationship with other tables. Problem occurred when i tried to insert new users with Hasura mutation:
Uniqueness violation. duplicate key value violates unique constraint "users_pkey"
What I assume happened is: My PK on users is not default type integer/auto-increment , it is:
id - integer, primary key, unique, default: nextval('users_id_seq'::regclass)
and when i try to insert new row it tries to assign it latest PK that Hasura remembers it inserted and user with that PK is already imported from MySql DB.
Is there some way to edit my table PK so it is integer/auto-increment or someone has some creative solution. Thanks in advance!
You need to adjust the value of the sequence seed with this SQL command :
ALTER SEQUENCE users_id_seq RESTART WITH ...`
The value that you must give to this SQL command is the MAX(id) + 1 of your table.