PostgreSQL sends a specific error code when you try to insert a duplicate key, and Go can read it
When you insert a row into PostgreSQL that violates a unique constraint, the database returns error code 23505. Your Go program receives this as part of the error message, and you can extract it to handle duplicates differently from other database errors. This matters because duplicate key errors are often expected — you might want to update the row instead, log it differently, or simply skip it — while other errors usually mean something is broken.
PostgreSQL's pq driver (the most common Go PostgreSQL library) wraps this error code in a way you can check with a type assertion. The error code lives in a field you can access directly, so you do not have to parse the error message as a string.
Key Takeaways
- PostgreSQL returns error code 23505 for unique constraint violations, including duplicate primary keys.
- The pq driver exposes this code in the Error.Code field, which you can check with a type assertion in your Go code.
- You check for the duplicate key error by converting the error to *pq.Error and comparing Code == "23505".
- Other constraint violations return different codes — foreign key violations are 23503, not-null violations are 23502 — so you can handle each one separately.
Reading the error code in Go
When you execute an INSERT or UPDATE that hits a duplicate key constraint, the pq driver returns an error. To check whether that error is a duplicate key violation, you use a type assertion to convert the generic error into a *pq.Error, then read the Code field.
Here is the pattern you use:
if err != nil { if pgErr, ok := err.(*pq.Error); ok { if pgErr.Code == "23505" { // Handle duplicate key } } }
The type assertion checks whether the error is actually a PostgreSQL error (it might be a connection error or something else). If it is, you can read the Code field and compare it to the string "23505". This is safer and clearer than trying to parse the error message, because the message text can change between PostgreSQL versions.
A complete example with INSERT
Here is a function that inserts a user and handles the duplicate key case by returning a custom error:
func InsertUser(db *sql.DB, id int, name string) error { _, err := db.Exec("INSERT INTO users (id, name) VALUES ($1, $2)", id, name) if err != nil { if pgErr, ok := err.(*pq.Error); ok && pgErr.Code == "23505" { return fmt.Errorf("user with id %d already exists", id) } return err } return nil }
If the INSERT fails with a duplicate key error, the function returns a message that says the user already exists. Any other database error gets returned as-is, so your calling code can see connection problems or syntax errors. This pattern lets you treat duplicates as a normal case rather than an exception.
Other PostgreSQL constraint codes you might see
PostgreSQL has different error codes for different constraint violations. If you want to handle multiple types of constraint errors, you can check for several codes:
| Error Code | Meaning | When it happens |
|---|---|---|
| 23505 | Unique violation | Duplicate primary key or unique constraint |
| 23503 | Foreign key violation | You reference a row that does not exist in another table |
| 23502 | Not-null violation | You try to insert NULL into a NOT NULL column |
| 23514 | Check constraint violation | A value fails a CHECK constraint |
You can check for any of these the same way — just compare pgErr.Code to the string you are looking for. This is useful if you want to give the user a specific message for each type of problem.
Handling duplicates with an upsert instead
Instead of checking for the error after the fact, you can use PostgreSQL's ON CONFLICT clause to handle duplicates in the INSERT statement itself. This avoids the error entirely:
INSERT INTO users (id, name) VALUES ($1, $2) ON CONFLICT (id) DO UPDATE SET name = $2
This inserts the row if the id does not exist, or updates the name if it does. Your Go code does not need to check for error code 23505 because the database handles the conflict. This is often cleaner than checking the error, especially if you know duplicates will happen regularly.
The ON CONFLICT clause requires you to name the column that has the unique constraint. If you have a primary key, use the column name. If you have a unique index on multiple columns, list them all in the DO CONFLICT clause.
Make sure you are importing pq correctly
To use the *pq.Error type, you need to import the pq driver. Most Go code imports it like this:
import ( "database/sql" _ "github.com/lib/pq" )
The underscore means you are importing the driver for its side effects (it registers itself with the sql package) but not using it directly in your code. However, to check the error code, you do need to import pq without the underscore so you can use the pq.Error type:
import ( "database/sql" "github.com/lib/pq" )
If you forget this import, the type assertion will not work and your code will not compile.
Frequently Asked Questions
What if I am using a different PostgreSQL driver for Go?
The pq driver is the most common, but other drivers like pgx work differently. With pgx, you check the error using pgconn.PgError and read the SQLState field instead of Code. The error code is still 23505, but the way you extract it changes. Check your driver's documentation for the exact pattern.
Can I check the error code without importing pq?
You can parse the error message as a string, but it is not reliable because the message format can change. The type assertion method is better because it reads the actual error code that PostgreSQL sent. If you cannot import pq for some reason, you would have to parse the error string, which is fragile.
Does the error code change if I have a composite unique constraint?
No. Whether the unique constraint is on a single column or multiple columns, PostgreSQL returns code 23505. The Constraint field in the pq.Error tells you which constraint was violated, so you can tell them apart if you need to.
What happens if the duplicate key error occurs in a transaction?
The error code is the same. However, once a constraint violation happens inside a transaction, that transaction is marked as failed and you must roll it back before you can run any other statements. You cannot just catch the error and keep going — you have to call Rollback() on the transaction first.
Can I use error code 23505 to catch duplicates in an UPDATE statement?
Yes. If an UPDATE statement tries to change a value to something that violates a unique constraint, PostgreSQL returns the same error code 23505. The pattern for checking it is identical.