PostgreSQL: Unique violation: 7 ERROR: duplicate key value violates unique constraint "users_pkey"

Question

I'm using psql in my Laravel App. I'm trying to create my user, and I keep getting this error

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

---

Here what I did to store 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();

---

Here 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 that I'm not suppose to?

What I am having right now used to work when I hook my app with MySQL.

Any hints / suggestions will mean a lot to me.

---

Screenshot

Solution 1

Postgres handles auto incrementing a little differently than MySQL does. In Postgres, when you create the serial field, you are also creating a sequence field that is keeping track of the id to use. This sequence field is going to start out with a value of 1.

When you insert a new record into the table, if you don't specify the id field, it will use the value of the sequence, and then increment the sequence. However, if you do specify the id field, then the sequence is not used, and it is not updated, either.

I'm assuming that when you moved over to Postgres, you seeded or imported some existing users, along with their existing ids. When you created these user records with their ids, the sequence was not used, and therefore it was never updated.

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

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

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

---

FYI, this is not an issue in MySQL because MySQL automatically updates the auto increment sequence to the largest column value when a value is manually inserted into the auto incrementing field.

Solution 2

The following code provides a way to do this in Laravel which is what the OP is using.

// Get all the tables from your database
$tables = \DB::select('SELECT table_name FROM information_schema.tables WHERE table_schema = \'public\' ORDER BY table_name;'); 

// Set the tables in the database you would like to ignore
$ignores = array('admin_setting', 'model_has_permissions', 'model_has_roles', 'password_resets', 'role_has_permissions', 'sessions'); 

//loop through the tables
foreach ($tables as $table) { 

   // if the table is not to be ignored then:
   if (!in_array($table->table_name, $ignores)) { 

       //Get the max id from that table and add 1 to it
       $seq = \DB::table($table->table_name)->max('id') + 1; 

       // alter the sequence to now RESTART WITH the new sequence index from above        
       \DB::select('ALTER SEQUENCE ' . $table->table_name . '_id_seq RESTART WITH ' . $seq); 

    }

}

Note - Using ALTER SEQUENCE "blocks concurrent transactions". Consider using the SQL statement from alternative solution provided above if this is not desired.

Solution 3

This error usually occurs when you export a table and import it into a different database.

The above methods work, but unfortunately they don't work on my system, The system I use is laravel and postgres.

I solved my problem by emptying my table

TRUNCATE TABLE table_name;

Solution 4

You can also edit every table sequence in PostgreSQL with DB management software. For example I use pgAdmin4. Just check what is the latest primary key for that table and then go to your DB -> Schemas -> 1.3 Sequences -> table -> Properties -> Definition and edit field Current value.

View more solutions