How PostgreSQL Tells You When a Record Already Exists

PostgreSQL returns a specific error code when you try to insert a duplicate key

When you insert a record into a PostgreSQL database and that record violates a unique constraint — meaning a value already exists that should be unique — PostgreSQL stops the insert and returns error code 23505. This is the standard SQL state code for a unique violation. In Go, you can check for this code and handle it differently than other database errors, like connection failures or syntax problems.

The error code itself is a five-character string that PostgreSQL uses to categorize what went wrong. The first two characters, 23, mean "integrity constraint violation." The last three characters, 505, narrow that down to "unique violation." When your Go program receives this code, it knows the insert failed because of a duplicate, not because the database is down or the query is malformed.

Key Takeaways

PostgreSQL returns error code 23505 when an insert violates a unique constraint, and you can check for this code in your Go program.
The pq library, which is the standard PostgreSQL driver for Go, exposes this error code as a string you can compare directly.
You extract the code by type-asserting the error to a pq.Error and reading its Code field.
Checking for 23505 lets you handle duplicates gracefully — for example, by returning a user-friendly message instead of crashing.

How to check for the duplicate key error in Go

The most common PostgreSQL driver for Go is called pq. When a duplicate key error occurs, pq wraps it in a structure that holds the error code. To read that code, you type-assert the error to a pq.Error and check its Code field.

Here is the pattern: after you run an insert query with db.Exec() or db.QueryRow(), check if the error is not nil. Then type-assert it to pq.Error. If the assertion succeeds, read the Code field and compare it to the string "23505".

A real example looks like this:

import "github.com/lib/pq" err := db.QueryRow("INSERT INTO users (email) VALUES ($1) RETURNING id", email).Scan(&id) if err != nil { if pgErr, ok := err.(*pq.Error); ok && pgErr.Code == "23505" { return fmt.Errorf("this email is already registered") } return err }

The type assertion err.(*pq.Error) checks whether the error is actually a PostgreSQL error. If it is, you get the pq.Error object. Then you check whether its Code field equals "23505". If both conditions are true, you know it is a duplicate key violation and can respond accordingly.

What happens if you do not check for the code

If you do not check for error code 23505, your program will treat a duplicate key the same way it treats any other database error. That usually means logging the raw PostgreSQL message and returning a generic error to the user. The user sees something like "pq: duplicate key value violates unique constraint 'users_email_key'" instead of "this email is already registered."

More importantly, you lose the ability to decide what to do. Sometimes a duplicate is expected — for example, if a user tries to add a favorite twice, you might want to silently ignore it. Other times a duplicate is an error that should be reported. By checking the code, you can handle each case differently.

Other error codes you might encounter

PostgreSQL uses other error codes for different problems. Code 23502 means a NOT NULL constraint was violated — you tried to insert a null value into a column that does not allow it. Code 23503 means a foreign key constraint failed — you tried to reference a record that does not exist. Code 23514 means a check constraint failed — the value you inserted did not pass a validation rule you defined.

You can check for any of these codes the same way you check for 23505. If your insert can fail in multiple ways and you want to give the user a specific message for each, check the code and respond accordingly. The PostgreSQL documentation lists all error codes, but the most common ones in process code are 23505, 23502, and 23503.

Using pgx instead of pq

Some Go projects use pgx instead of pq. The pgx driver is newer and has some performance advantages. With pgx, you check for duplicate key errors differently. You import the pgconn package and type-assert to pgconn.PgError, then read its SQLState field instead of Code.

The SQLState field holds the same five-character code — "23505" for duplicate key — but the type and package names are different. If you are using pgx, the pattern is:

import "github.com/jackc/pgx/v5/pgconn" err := db.QueryRow(ctx, "INSERT INTO users (email) VALUES ($1) RETURNING id", email).Scan(&id) if err != nil { var pgErr *pgconn.PgError if errors.As(err, &pgErr) && pgErr.SQLState == "23505" { return fmt.Errorf("this email is already registered") } return err }

Notice that pgx uses errors.As() instead of a type assertion. This is the modern Go way to unwrap errors. Both approaches work; the difference is which driver you chose when you set up your database connection.

Why PostgreSQL uses standard error codes

PostgreSQL does not invent its own error codes. It uses the SQLSTATE standard, which is part of the SQL specification. This means the same code 23505 means the same thing in PostgreSQL, MySQL, Oracle, and other databases that follow the standard. If you write code that checks for 23505, that logic is portable — you could swap databases later and the error handling would still work.

The standard exists because database errors are common and predictable. Developers need a reliable way to tell the difference between "the data is invalid" and "the server is down" and "the query syntax is wrong." By using standard codes, PostgreSQL makes that possible.

Frequently Asked Questions

Can I catch the duplicate key error without checking the code?

You can check whether the error message contains the word "duplicate" or "unique", but that is fragile. Error messages change between PostgreSQL versions and can be translated into other languages. Checking the code is more reliable because the code never changes.

What if I want to ignore duplicate key errors and continue?

You can check for code 23505 and straightforward not return an error. For example, if you are inserting a favorite and the user already favorited it, you might just return success without doing anything. The key is deciding whether a duplicate is an error or expected behavior for your use case.

Does the error code work the same way with prepared statements?

Yes. Whether you use prepared statements or inline queries, PostgreSQL returns the same error code. The pq or pgx driver extracts it the same way. Prepared statements are safer against SQL injection, so use them when you can.

What if the unique constraint has a custom name?

The error code is still 23505 regardless of the constraint name. The pq.Error object also has a Constraint field that holds the name, so you can check which specific constraint was violated if you need to. But for most cases, just checking the code is enough.

Can I get the duplicate value that caused the error?

PostgreSQL does not return the actual value in the error code or message for security reasons — you do not want to leak sensitive data like passwords or API keys in error logs. You can infer which column caused the problem from the constraint name, but not the value itself.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.