Handling PostgreSQL Errors

PostgresSQL is one of the most popular relational database systems and most developers work with it daily.

Understanding how to retrieve and handle database errors can save us from making unnecessary queries or handling extra logic in our code. I'll go over the main points to note and then list a few practical use cases at the end of the article.

In this article, I will be using Go and lib/pq, but the same techniques also apply to most languages and drivers.

Here are the main points to note:

1. PostgreSQL Errors have codes

Every PostgreSQL error has an error code (see the full list). Whatever library or language you use to access Postgres, you should be able to retrieve the error code to handle specific cases.

For example, if our email column in our users table has a unique constraint, we know that a new insert/update query will fail if that constraint is violated. From the documentation, we can see that the error code for a unique_violation is 23505, and so we can check for that like this:

_, err = db.Exec("INSERT INTO users(email) VALUES($1)", "existing@example.com")
// Check specifically for a unique constraint error
var pqErr *pq.Error
if errors.As(err, &pqErr) && pqErr.Code == "23505" {
    // handle the violation
}

// Check if it was a different error
if err != nil {
    panic(err)
}

2. PostgreSQL Errors can contain even more information

In many cases, the error code is not enough. Thankfully, PostgresSQL returns even more information as part of the error diagnostics. Usually, your driver will parse this information for you, but you have to know how to access it. See documentation.

For example, we may have multiple unique constraints(23505), or multiple check constraints(23514) that could be violated by our query.

In the cases, we may want to get the specific constraint name that was violated.

_, err = db.Exec("INSERT INTO users(email) VALUES($1)", "existing@example.com")
// Check specifically for a violation of the "unique_user_email" constraint
var pqErr *pq.Error
if errors.As(err, &pqErr) && pqErr.Constraint == "unique_user_email" {
    // handle the violation
}

// Check if it was a different error
if err != nil {
    panic(err)
}

Some practical use cases

• If our database table has a unique constraint on the email column, instead of doing an initial query to check if a user exists with that email address, before inserting a new user, we can directly add the email, and if we get an error understand that a unique constraint violation on that column means that the email address existed before.
• If we have a check constraint to make sure product quantities are not negative, we can freely add/subtract from the quantity and handle the check constraint violation to know if the quantity went negative.