Postgresexception: 23505: duplicate key value violates unique constraint

PostgreSQLException: 23505

This error with code 23505 is a unique constraint violation in PostgreSQL, which means that an insert or update statement has resulted in a duplicate key value that violates a unique constraint defined on a column or a set of columns.

How does it occur?

Let’s assume you have a table named “users” with a unique constraint on the “email” column to ensure all emails are unique. If you try to insert or update a row with a duplicate email value, you will encounter the 23505 error.

Example:

Consider the following table structure:

      CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        email VARCHAR(255) UNIQUE,
        username VARCHAR(50)
      );
    

Now, suppose we have the following data in the “users” table:

      id | email             | username
      ---------------------------------------
      1  | john@example.com  | john
      2  | jane@example.com  | jane
    

If we attempt to insert another row with the email “john@example.com”, we will encounter the 23505 error. For example:

      INSERT INTO users (email, username) VALUES ('john@example.com', 'newjohn');
    

How to resolve it?

To resolve this error, you need to make sure that you are not inserting or updating any duplicate key values. In our example, you can either provide a different and unique email value for the new row or update the existing row with a different email. Alternatively, you can also remove the unique constraint on the email column if it is not required.

Here’s how an update query could be used to resolve the error in our example:

      UPDATE users SET email = 'newjohn@example.com' WHERE id = 1;