pgx Error Handling With pgconn PgError Codes in Go Code
Read pgx server errors with pgconn.PgError: SQLSTATE codes, constraint names, details and positions, pgerrcode classes, and connection errors
When a pgx call fails, the error tells you far more than its message, if you ask for it in the right way. The server's own error, with a five character code, the name of the constraint that failed, the offending table and column, a detail line and a hint, travels as a structured value, and a program that only prints err.Error() throws most of it away. This article shows what each field contains for common failures, which package gives you readable names for the codes, and how connection level failures differ, all against PostgreSQL 18.6 from Go 1.25.6.
Tested with Go 1.25.6, pgx v5.11.0, pgerrcode, PostgreSQL 18.6 in Docker, macOS on Apple Silicon (arm64)
It belongs to the database cornerstone. It is the pgx specific half of handling database errors, and the unique violation case is developed in unique violations.
The type
package main
import (
"context"
"errors"
"fmt"
"log"
"github.com/jackc/pgerrcode"
"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgconn"
"github.com/jackc/pgx/v5/pgxpool"
)
const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"
func must(err error) {
if err != nil {
log.Fatal(err)
}
}
func main() {
ctx := context.Background()
pool, err := pgxpool.New(ctx, dsn)
must(err)
defer pool.Close()
for _, q := range []string{
"drop table if exists kid674, par674",
"create table par674 (id int primary key, email text unique not null, age int check (age >= 0))",
"create table kid674 (id int primary key, par int references par674 on delete restrict)",
"insert into par674 values (1, '[email protected]', 30)",
"insert into kid674 values (1, 1)",
} {
_, err := pool.Exec(ctx, q)
must(err)
}
show := func(label string, err error) {
var pg *pgconn.PgError
if !errors.As(err, &pg) {
fmt.Printf("%-34s not a PgError: %v\n", label, err)
return
}
fmt.Printf("%-34s code %s severity %s\n", label, pg.Code, pg.Severity)
fmt.Printf(" message: %s\n", pg.Message)
if pg.Detail != "" {
fmt.Printf(" detail: %s\n", pg.Detail)
}
if pg.Hint != "" {
fmt.Printf(" hint: %s\n", pg.Hint)
}
if pg.SchemaName != "" || pg.TableName != "" {
fmt.Printf(" schema, table, column, constraint: %q %q %q %q\n", pg.SchemaName, pg.TableName, pg.ColumnName, pg.ConstraintName)
}
if pg.Position > 0 {
fmt.Printf(" position: %d (character in the statement)\n", pg.Position)
}
}
_, err = pool.Exec(ctx, "insert into par674 values (2, '[email protected]', 1)")
show("unique violation", err)
fmt.Println(" matches pgerrcode.UniqueViolation:", pgerrcode.IsIntegrityConstraintViolation("23505"), "| code equal:", err.(*pgconn.PgError).Code == pgerrcode.UniqueViolation)
_, err = pool.Exec(ctx, "insert into par674 values (3, '[email protected]', -1)")
show("check violation", err)
_, err = pool.Exec(ctx, "delete from par674 where id = 1")
show("foreign key (restrict)", err)
_, err = pool.Exec(ctx, "insert into par674 (id, age) values (4, 1)")
show("not null violation", err)
_, err = pool.Exec(ctx, "select * from par674 wher id = 1")
show("syntax error", err)
_, err = pool.Exec(ctx, "select nope from par674")
show("undefined column", err)
_, err = pool.Exec(ctx, "select 1/0")
show("division by zero", err)
// Classes: the first two characters group codes into families.
codes := []string{pgerrcode.UniqueViolation, pgerrcode.ForeignKeyViolation, pgerrcode.SerializationFailure, pgerrcode.DeadlockDetected, pgerrcode.ConnectionFailure, pgerrcode.SyntaxError, pgerrcode.UndefinedTable}
fmt.Println("classes of a few codes:")
for _, c := range codes {
fmt.Printf(" %s integrity %-5v txn rollback %-5v connection %-5v syntax or access %-5v\n", c,
pgerrcode.IsIntegrityConstraintViolation(c), pgerrcode.IsTransactionRollback(c), pgerrcode.IsConnectionException(c), pgerrcode.IsSyntaxErrororAccessRuleViolation(c))
}
// A connection error is not a PgError.
bad, err := pgxpool.New(ctx, "postgres://postgres:[email protected]:55432/lab?sslmode=disable")
must(err)
_, err = bad.Exec(ctx, "select 1")
show("wrong password", err)
bad.Close()
closed, err := pgxpool.New(ctx, "postgres://postgres:[email protected]:1/lab?sslmode=disable&connect_timeout=1")
must(err)
_, err = closed.Exec(ctx, "select 1")
var pe *pgconn.ConnectError
fmt.Printf("closed port: ConnectError %v | PgError %v\n", errors.As(err, &pe), errors.As(err, new(*pgconn.PgError)))
closed.Close()
// SafeToRetry says whether the driver knows the statement never reached the server.
_, err = closed.Exec(ctx, "select 1")
fmt.Println("pgconn.SafeToRetry on a refused connection:", pgconn.SafeToRetry(err))
_ = pgx.ErrNoRows
}unique violation code 23505 severity ERROR
message: duplicate key value violates unique constraint "par674_email_key"
detail: Key (email)=([email protected]) already exists.
schema, table, column, constraint: "public" "par674" "" "par674_email_key"
matches pgerrcode.UniqueViolation: true | code equal: true
check violation code 23514 severity ERROR
message: new row for relation "par674" violates check constraint "par674_age_check"
detail: Failing row contains (3, [email protected], -1).
schema, table, column, constraint: "public" "par674" "" "par674_age_check"
foreign key (restrict) code 23001 severity ERROR
message: update or delete on table "par674" violates RESTRICT setting of foreign key constraint "kid674_par_fkey" on table "kid674"
detail: Key (id)=(1) is referenced from table "kid674".
schema, table, column, constraint: "public" "kid674" "" "kid674_par_fkey"
not null violation code 23502 severity ERROR
message: null value in column "email" of relation "par674" violates not-null constraint
detail: Failing row contains (4, null, 1).
schema, table, column, constraint: "public" "par674" "email" ""
syntax error code 42601 severity ERROR
message: syntax error at or near "id"
position: 27 (character in the statement)
undefined column code 42703 severity ERROR
message: column "nope" does not exist
position: 8 (character in the statement)
division by zero code 22012 severity ERROR
message: division by zero
classes of a few codes:
23505 integrity true txn rollback false connection false syntax or access false
23503 integrity true txn rollback false connection false syntax or access false
40001 integrity false txn rollback true connection false syntax or access false
40P01 integrity false txn rollback true connection false syntax or access false
08006 integrity false txn rollback false connection true syntax or access false
42601 integrity false txn rollback false connection false syntax or access true
42P01 integrity false txn rollback false connection false syntax or access true
wrong password code 28P01 severity FATAL
message: password authentication failed for user "postgres"
closed port: ConnectError true | PgError false
pgconn.SafeToRetry on a refused connection: falseA failure reported by the server is a *pgconn.PgError. Get it with errors.As, never a type assertion, since pgx may wrap it:
var pg *pgconn.PgError
if errors.As(err, &pg) {
// pg.Code, pg.ConstraintName, ...
}The fields worth knowing: Code (the SQLSTATE), Severity (ERROR, FATAL, PANIC), Message, Detail, Hint, Position, and the object names SchemaName, TableName, ColumnName, DataTypeName and ConstraintName. Not every field is filled for every error: the server sends what applies.
What the fields contain
The lab provokes seven failures and prints the fields.
A unique violation (23505): the message names the index, the detail gives the column and value (Key (email)=([email protected]) already exists.), and the constraint name is par674_email_key, along with schema and table. The constraint name is how you tell two unique rules apart.
A check violation (23514): the constraint name is par674_age_check, and the detail shows the whole row that failed: Failing row contains (3, [email protected], -1). Be careful with the detail. It contains the data, which may be personal, so log it at a level that fits and do not return it to users.
A foreign key violation on delete (23001, restrict_violation) when a restrict rule stops a delete: the message names the constraint and the referencing table, and the detail says Key (id)=(1) is referenced from table "kid674". Note that this is code 23001 and not 23503, which is the more common insert side violation. The same rule produces different codes depending on which side triggers it, so list both in your mapping.
A not null violation (23502): ColumnName is email, and ConstraintName is empty, since not null is a column property and not a named constraint. So a mapping that looks only at constraint names misses it; for not null, map by column.
A syntax error (42601) and an undefined column (42703) include a Position: the character offset of the problem in the statement text (27 in select * from par674 wher id = 1, where wher id begins). Log it with the statement and the mistake is easy to find. These two are bugs in the program, not conditions to handle; let them become 500s with the full error in the log.
Division by zero (22012) has no position and no names: it is a data exception during evaluation, class 22.
Codes and classes
A SQLSTATE is two characters of class followed by three of detail. The class groups related conditions, which is often what you want to switch on: class 23 is integrity constraint violations, class 40 is transaction rollbacks (serialization failure 40001 and deadlock 40P01), class 08 is connection exceptions, class 42 is syntax errors and access rule violations, class 22 is data exceptions, and class 53 is insufficient resources (53300 too many connections).
The github.com/jackc/pgerrcode package makes codes readable and gives you the class tests. Constants such as pgerrcode.UniqueViolation and pgerrcode.SerializationFailure replace magic strings, and helpers such as IsIntegrityConstraintViolation, IsTransactionRollback, IsConnectionException and IsSyntaxErrororAccessRuleViolation (the name has that spelling in the package) test a whole class. The lab prints a small table: 23505 and 23503 are integrity violations, 40001 and 40P01 are both transaction rollbacks, 08006 is a connection exception and 42601 and 42P01 are in the syntax or access class. A retry loop can test IsTransactionRollback(pg.Code) and cover serialization failures and deadlocks in one line, as retrying serialization failures does.
Connection errors are not PgErrors
Failures before a session exists, and failures of the network, do not come from the server's error protocol. The lab shows both sides of the line. A wrong password is reported by the server during authentication, so it is a PgError, code 28P01, severity FATAL: the server speaks, then closes. A refused connection, a closed port, never reached a server, so the error is a *pgconn.ConnectError, errors.As(err, &connectErr) is true and a PgError is not. Handle both: a service that checks only PgError will treat "the database is down" as an unknown error, and one that checks only network errors will mistake bad credentials for an outage.
The helper pgconn.SafeToRetry(err) says whether the driver knows that the statement was never sent and can be retried without risk of a double write. On the refused connection above it returned false, so do not assume connection errors are retryable by that test alone: for a connect failure you are retrying the connection, not a statement, which is safe, but the function is for errors during a statement. For a lost connection in the middle of a write, the outcome is unknown, and only idempotent writes should be retried.
A mapping table
Keep the mapping in one place, keyed by what you can act on:
| Condition | Test | Meaning for the program |
|---|---|---|
| Unique violation | Code == "23505", name from ConstraintName | Conflict; which field |
| Other integrity | class 23 | Invalid data or a missing parent |
| Not null | Code == "23502", field from ColumnName | Invalid: required field |
| Serialization, deadlock | class 40 | Retry the transaction |
| Statement cancelled | 57014 | Timeout or cancel |
| Connection exception, too many connections | class 08, 53300 | Unavailable; back off |
| Syntax, undefined object | class 42 | A bug; alert |
| Everything else | none | 500, log all fields |
Log the whole PgError at the boundary where you turn it into a response: code, constraint, table, column, detail and position, with the operation name. Return a stable, generic message to the caller. The fields are documented in the pgconn package reference, the codes in the PostgreSQL manual's appendix of error codes, and the constants in the pgerrcode package.
