Geek Answers Handbook

Unique violation: 7 ERROR: duplicate key value violates the unique restriction "users_pkey"

I am using psql in my Laravel application. I am trying to create my user and I keep getting this error

Unique violation: 7 ERROR: duplicate key value violates the unique restriction "users_pkey"

Here I did to save my user

$user = User::where('account_id','=',$id)->first(); $user->email = $account->email_address; $user->fb_email = ''; $user->tw_email = ''; $user->fb_access_token = ''; $user->fb_profile_id = ''; $user->fb_page_id = ''; $user->fb_username = ''; $user->save();

This is how I created my users table

 CREATE TABLE "users" ( "id" serial NOT NULL , "account_id" varchar(255) NOT NULL , "email" varchar(255) NOT NULL , "fb_email" varchar(255) NOT NULL , "tw_email" varchar(255) NOT NULL , "created_at" timestamp(0) without time zone, "updated_at" timestamp(0) without time zone, "fb_access_token" varchar(255) NOT NULL , "fb_profile_id" varchar(255) NOT NULL , "fb_page_id" varchar(255) NOT NULL , "fb_username" varchar(255) NOT NULL , PRIMARY KEY ("id") );

Did I do anything I did not guess?

What I am using now when I connect my application using MySQL .

Any hints / suggestions will mean a lot to me.

Screenshot

enter image description here

+6
source share
1 answer

Postgres handles auto-addition a little differently than MySQL. In Postgres, when you create a serial field, you also create a sequence field that tracks the identifier used. This sequence field will begin with a value of 1.

When you insert a new record into the table, if you do not specify an id field, it will use the sequence value, and then increase the sequence. However, if you specify the id field, the sequence will not be used, and it will also not be updated.

I assume that when you switched to Postgres, you sowed or imported some existing users along with their existing identifiers. When you created these user records with their identifiers, the sequence was not used, and therefore it was never updated.

So, if, for example, you imported 10 users, you have users with identifiers 1-10, but your sequence is still 1. When you try to create a new user without specifying an identifier, it pulls the value from sequence (1), and you get a unique violation because you already have a user with id 1.

To fix the problem, you need to set the users_id_seq sequence users_id_seq to the MAX (id) of your existing users. You can read this question / answer for more information about resetting a sequence, but you can also try something like (untested):

 SELECT setval(pg_get_serial_sequence('users', 'id'), coalesce(max(id),1), false) FROM users;

FYI, this is not a problem in MySQL, because MySQL automatically updates the auto-increment sequence to the largest column value when the value is entered manually in the auto-increment field.

+13
source

More articles:


All Articles