Why Pg::Uniqueviolation: Error: Duplicate Key Value Violates Unique Constraint

rake db:drop

rake db:create

rake db:migrate

Rails PG::UniqueViolation: ERROR: duplicate key value violates unique constraint table_pkey

You're using sequential integer id's for your table according to the migration. This works well enough if you allow the database to assign id's for you. Every time a new record comes in, database takes the next number on the list and assigns it to that record (simplifying here).

Lets assume the database id sequence is currently at 3 and the records you imported have ids 4, 37 and 143025. Inserting a new record to the database, database says id is 3, all good, sequence is now at 4. Inserting another one, database says id is 4. Trying to insert it, but there already is a 4 in the database.

PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "table_pkey"

A few possible solutions:

1. After importing, change the database id sequence to something bigger than the largest id you imported. (hacky, but works) Postgres manually alter sequence
2. Import the items without hardcoding their id-s. (complicated)
3. Change your database to use uuid-s instead of integer id-s (architectural change, difficult if the app is live, best solution if you're still in development)
4. Use a proper database backup system rather than building your own. pg_dump

How to fix duplicate key value violates unique constraint error in Postges 9+

You can use on conflict:

INSERT INTO session (workplace,ipaddress,logintime,loggeduser)
    SELECT 'WIN-N9BSKUNKBC8',     
          inet_client_addr()::CHAR(14),current_timestamp::CHAR(28),
CURRENT_USER 
    ON CONFLICT DO NOTHING;

Given that you have only one row going in, my guess is that the problem is due to concurrency issues.

Error: 'duplicate key value violates unique constraint' even when checking with 'where not in'

The error message is clear enough: you cannot insert because it violates a unique constraint that is defined in the table.

Since you are already checking for dups on the primary key, it is higly likely that your table has another unique constraint set up, which forbids the insert operation. That cannot be told for sure since your did not share the definition of your table.

You could change the query to check for that constraint as well before inserting.

Another option would be to use handy Postgres ON CONFLICT DO NOTHING clause to do the work for you. From the documentation:

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. [...] ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action.

Consider this simpler query:

INSERT INTO db1.x (a,b,c,d)
    SELECT y.a, y.b, y.c, z.p FROM db2.y INNER JOIN db2.z ON y.a = z.a
ON CONFLICT DO NOTHING;

Note: implicit joins have fallen out of favor long ago (although perfectly functionals, they tend to make te query harder to understand); I modified that part of the query to use an explicit join.

ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR: duplicate key value violates unique constraint

The error is because create_list(:shift, 20) is trying to create 20 users, all with the same phone number 1-444-555-8888, and there is a uniqueness condition that prevents this.

Change the factory definition such that it creates unique phone numbers for each users, and the error should go away.

Here's one way to do that:

phone_number { rand(10**9..10**10)}

Reference: Use a factory's sequence to generate unique phone numbers

Since your requirement is to create 20 shifts for one user, try the following:

@user = create(:user)
create_list(:shift, 20, user: @user)

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

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.

PostgreSQL: ERROR: duplicate key value violates unique constraint

PostgreSQL does not put quotes around the values in that error message. So the quote marks you see are part of the literal value being inserted.

So to see the existing row which conflicts, you would need to do:

SELECT * FROM admin.file_status where file_name='''20210420125933_NOTIFICATION_1_1.txt'''

or

SELECT * FROM admin.file_status where file_name=$$'20210420125933_NOTIFICATION_1_1.txt'$$

or similar.

---

Related Topics