Does PostgreSQL Return an SQL Code for Duplicate Key in Golang?

When building backend applications using PostgreSQL with Golang, handling duplicate key errors correctly is essential for maintaining data integrity and delivering predictable API behavior. This knowledge base article explains whether PostgreSQL returns an SQL error code for duplicate key violations and how to properly handle it in Go applications.

Quick Answer

Yes. PostgreSQL returns a specific SQLSTATE error code for duplicate key violations.

SQLSTATE Code: 23505

This error represents a unique_violation, triggered when a UNIQUE constraint or PRIMARY KEY constraint is violated.

What Triggers a Duplicate Key Error in PostgreSQL?

PostgreSQL raises error 23505 when:

• A PRIMARY KEY constraint is violated
• A UNIQUE constraint is violated
• A UNIQUE INDEX is violated

Example

INSERT INTO users (email)
VALUES ('[email protected]');

If email has a UNIQUE constraint and the value already exists, PostgreSQL returns:

ERROR: duplicate key value violates unique constraint "users_email_key"
SQLSTATE: 23505

---

Understanding SQLSTATE Code 23505

PostgreSQL follows the SQL standard for error codes.

• The class 23 represents Integrity Constraint Violations.
• The specific code 23505 represents Unique Violation.

Using SQLSTATE codes is recommended because they are stable and standardized, unlike error message strings that may vary.

---

Handling Duplicate Key Errors in Golang

In Go applications, PostgreSQL errors can be accessed via driver-specific error types.

Using lib/pq

import (
    "database/sql"
    "github.com/lib/pq"
)

_, err := db.Exec("INSERT INTO users(email) VALUES($1)", email)
if err != nil {
    if pqErr, ok := err.(*pq.Error); ok {
        if pqErr.Code == "23505" {
            fmt.Println("Duplicate key detected")
        }
    }
}

---

Using pgx

import (
    "github.com/jackc/pgconn"
)

_, err := conn.Exec(ctx, "INSERT INTO users(email) VALUES($1)", email)
if err != nil {
    if pgErr, ok := err.(*pgconn.PgError); ok {
        if pgErr.Code == "23505" {
            fmt.Println("Duplicate key detected")
        }
    }
}

---

Instead:

Production Best Practices

Avoid checking for duplicate key errors using string matching such as:

if strings.Contains(err.Error(), "duplicate key")

• Check the SQLSTATE code (23505)
• Map it to a domain-specific error
• Return an appropriate HTTP status code (e.g., 409 Conflict in REST APIs)

Example

return errors.New("email already exists")

---

Preventing Duplicate Key Errors Using UPSERT

PostgreSQL supports ON CONFLICT, which allows safe handling of duplicate keys.

Option 1: Do Nothing

INSERT INTO users(email)
VALUES ('[email protected]')
ON CONFLICT (email)
DO NOTHING;

Option 2: Update Existing Record

INSERT INTO users(email)
VALUES ('[email protected]')
ON CONFLICT (email)
DO UPDATE SET updated_at = NOW();

---

Related PostgreSQL Error Codes

SQLSTATE Code	Description
23505	Unique violation
23503	Foreign key violation
23502	Not null violation
22001	String data too long
42601	SQL syntax error

---

Frequently Asked Questions

Does PostgreSQL always return 23505 for duplicate key violations?

Yes. For UNIQUE and PRIMARY KEY constraint violations, PostgreSQL consistently returns SQLSTATE 23505.

Should I rely on error messages instead of SQLSTATE codes?

No. Error messages may vary. SQLSTATE codes are standardized and should be used for reliable error handling.

Does this work with both lib/pq and pgx?

Yes. Both drivers expose the SQLSTATE code through their respective error structures.

---

Final Thoughts

PostgreSQL returns SQLSTATE error code 23505 for duplicate key violations. In Golang, this can be reliably detected using driver-specific error types such as pq.Error or pgconn.PgError.

Proper handling ensures:

• Clean and predictable API responses
• Improved user experience
• Strong data integrity
• Production-grade reliability

This approach is recommended for all Go applications interacting with PostgreSQL in production environments.