41

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

enter image description here

Promise Preston
  • 24,334
  • 12
  • 145
  • 143
code-8
  • 54,650
  • 106
  • 352
  • 604
  • you don't have a users_pkey in your insert, and most likely didn't make it an auto-incrementing value in the DB, so it gets a default value, which happens to already exist elsewhere in the table, causing your error. – Marc B Jun 22 '16 at 14:20
  • It must be the diff between psql vs. mysql again. – code-8 Jun 22 '16 at 14:23
  • How can I make/user my `id` as my pkey ? is it possible ? – code-8 Jun 22 '16 at 14:24
  • https://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-SERIAL – wazelin Jun 22 '16 at 14:26

4 Answers4

129

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.

patricus
  • 59,488
  • 15
  • 143
  • 145
  • You're 100% correct, after I provided the id manually, now it works `perfectly` fine. – code-8 Jun 22 '16 at 16:55
  • 8
    Great explanation, unfortunately the code almost worked for me - it set the pointer to the last inserted element but not the next after the last so it still didn't work since PostgreSQL tried to add a new row using the last used id - so there should be probably `max(id)+1`, but before testing this suggestion I used another solution found elsewhere that worked for me: `SELECT setval('users_id_seq', (SELECT MAX(id) from "users"));` – Picard Jun 04 '18 at 22:34
  • 6
    that is the best answer I have ever gotten on Stack overflow. 100% correct. great detail – Martin Naughton Oct 06 '19 at 19:50
  • I added in the `+1` to the max id. When the third parameter to `setval()` is false, the next call to the sequence will return the value set by `setval()`. If you change the third param to `true`, or remove it, the next call to the sequence will return the value after the one one set by `setval()`. Since we're ensuring a default of 1 when there are no rows, we want the next call to the sequence to return what we pass into `setval()`, so we need to pass in the `max(id)+1` and the third param as `false`. – patricus Jul 20 '20 at 21:33
  • This is a really great answer and I was doing exactly this. Migrating from mySQL to Postgres. I was singing the latter's praises but this feature/bug is more than a nuisance. If i have concurrent connections all incrementing the memberId field that means i'd have to lock the db, get the next val and use that one. I feel like this defeats the whole purpose of auto incrementing/serial numbers. It's awful actually. – stingMantis Sep 15 '20 at 02:12
10

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.

Coola
  • 2,934
  • 2
  • 19
  • 43
hamed baftam
  • 109
  • 2
  • 2
  • Providing just plain code is not a good idea as it is hard to follow why this code should be the solution to the problem. Please explain in detail how your answer is going to solve the problem. – codedge Apr 30 '20 at 09:36
  • 1
    @codedge I edited the answer and provided context with comments per line. The answer seems to be good for the tool which the OP is using i.e. Laravel (PHP). I hope this helps others as it did me. – Coola Jan 20 '21 at 14:58
4

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;
İbrahim
  • 122
  • 1
  • 6
3

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.

AirwaveQ
  • 53
  • 5