Causes and Solutions of Primary Key Conflicts in PostgreSQL Database

When you are confidently executing an `INSERT` operation, a red error suddenly pops up:

> **ERROR: duplicate key value violates unique constraint "users_pkey"** **DETAIL: Key (id)=(10) already exists.**

This is probably one of the most common "breaking" moments for PostgreSQL developers. Primary key conflicts not only interrupt business processes but can also lead to data inconsistency if handled improperly.

Today, let's take an in-depth look: Why do primary key conflicts occur? How to quickly fix them when encountered? And how to completely avoid them in a production environment?

## Common Causes

The core logic of a primary key conflict is simple: **attempting to insert a value that already exists into the primary key column**. However, in practical applications, this situation is usually caused by the following hidden scenarios:

### A. Sequence Out of Sync

This is the most common reason. PostgreSQL's `SERIAL` or `IDENTITY` columns rely on an independent "sequence object" to generate auto-incrementing IDs.

- **Scenario**: You manually inserted a record with a specific ID (e.g., `INSERT INTO users (id, name) VALUES (10, 'Tom')`), but the sequence object is unaware that you used ID 10.
- **Consequence**: When the next auto-incrementing insert requests ID 10, a conflict occurs.

### B. Concurrency Conflicts

In high-concurrency environments, if two transactions simultaneously attempt to query the maximum ID and insert after adding 1 (instead of using the database's native auto-increment mechanism), they might get the same ID due to a race condition.

### C. Improper Data Migration and Recovery

After migrating data using `pg_dump` or `copy` commands, sometimes the current value (Last Value) of the sequence is not correctly reset, causing it to start counting from an old point.

## Solutions

### Solution 1: Manually Synchronize the Sequence

If the sequence has fallen behind due to manual data insertion, you need to update the sequence's current value to the next value after the maximum ID in the table.

1. Find the current maximum ID in the table:

   ```SQL
   SELECT max(id) FROM your_table_name;
   ```

2. Reset the sequence (assuming the maximum ID is 100):

   ```SQL
   -- Method A: Directly set it to the next available value
   SELECT setval('your_table_id_seq', 100); 
   
   -- Method B: Automatic repair (Recommended)
   SELECT setval(pg_get_serial_sequence('your_table_name', 'id'), (SELECT max(id) FROM your_table_name));
   ```

### Solution 2: Handle the Conflict

If you want to avoid errors when a conflict occurs, and instead update the existing record or simply ignore it, you can use PostgreSQL's powerful `UPSERT` syntax.

- **Update on conflict:**

  ```SQL
  INSERT INTO users (id, name) VALUES (10, 'New Name')
  ON CONFLICT (id) 
  DO UPDATE SET name = EXCLUDED.name;
  ```

- **Ignore on conflict:**

  ```SQL
  INSERT INTO users (id, name) VALUES (10, 'New Name')
  ON CONFLICT (id) DO NOTHING;
  ```

## How to Avoid It at the Source?

Prevention is better than cure. Following these principles during the architectural design phase can significantly reduce the probability of conflicts:

### 1. Never Manually Insert into Auto-Increment Columns

Unless performing specific data repairs, always let the database generate IDs automatically.

```SQL
-- Correct approach: Omit the ID column
INSERT INTO users (name) VALUES ('Alice');
```

### 2. Prefer `GENERATED ALWAYS AS IDENTITY`

For PostgreSQL 10 and above, it is recommended to use `IDENTITY` instead of `SERIAL`. It is more compliant with SQL standards and can enforce `ALWAYS` constraints to prevent accidental manual insertion.

```SQL
CREATE TABLE users (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT
);
```

### 3. Consider Using UUIDs

For distributed systems or high-concurrency scenarios, auto-incrementing integer IDs can easily become bottlenecks and are prone to conflicts. Using UUIDs can almost entirely eliminate the possibility of ID collisions.

```SQL
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT
);
```

### 4. Automated Checks After Migration

Develop the habit: After any large-scale data import, run a script to check if the sequences for all tables are aligned with their maximum IDs.

## Summary

PostgreSQL primary key conflicts are usually the result of a **disconnect between the sequence object and the table data**. The key to resolving them lies in resynchronizing the sequence via `setval` or enhancing the robustness of SQL statements using `ON CONFLICT`.