github.com/jackc/pgx/v5 v5.10.0: Inspect PostgreSQL server errors via pgconn.PgError structured fields, SQLState method, and error formatting

# pgconn.PgError Behavior in pgx v5.10.0

## Search Result

Prior search via `search_known_solution` returned `NO_SAFE_MATCH`.

## Pinned Release

- Package: `github.com/jackc/pgx/v5`
- Version: `v5.10.0`
- Symbol: `pgconn.PgError`

## Observed Behavior

1. **Error Formatting**:
   `(*pgconn.PgError).Error()` produces the formatted string `pe.Severity + ": " + pe.Message + " (SQLSTATE " + pe.Code + ")"`.
   Diagnostic fields such as `Detail`, `Hint`, `Where`, `SchemaName`, `TableName`, and `ConstraintName` are intentionally excluded from the string returned by `Error()`. Callers must read these structured fields directly from the `*pgconn.PgError` instance rather than parsing error text.

2. **SQLSTATE Lookup**:
   `(*pgconn.PgError).SQLState()` returns the 5-character PostgreSQL SQLSTATE error code (`pe.Code`), e.g., `"23505"` for unique violation or `"42P01"` for undefined table.

3. **Standard Error Unwrapping**:
   The pointer type `*pgconn.PgError` implements the standard Go `error` interface. When PostgreSQL errors are wrapped across application boundaries (e.g. `fmt.Errorf("query failed: %w", err)`), callers inspect and extract the structured error using `errors.As(err, &pgErr)` where `pgErr` is typed as `*pgconn.PgError`.

4. **Wire Protocol Conversion**:
   `pgconn.ErrorResponseToPgError` parses a PostgreSQL frontend/backend wire protocol message (`*pgproto3.ErrorResponse`) into a structured `*pgconn.PgError`, populating all error diagnostic fields, file position metadata, and schema/constraint details.

{"case":{"believed":"pgconn.PgError.Error() includes diagnostic details such as Detail, Hint, and ConstraintName in its formatted error string.","caseId":"case:sha256:41a845312976753202d400bbca9622a3387e7ca520ed491f0f61143c8dcbe12a","contract":["pgconn.PgError.Error() formats only the severity, message, and SQLSTATE code, leaving Detail, Hint, and constraint metadata accessible exclusively through struct fields","pgconn.PgError.SQLState() returns the 5-character SQL standard error code matching the Code field","errors.As successfully extracts *pgconn.PgError when wrapped inside standard error chains","pgconn.ErrorResponseToPgError maps wire protocol pgproto3.ErrorResponse fields into a *pgconn.PgError struct"],"goal":"Inspect PostgreSQL server errors via pgconn.PgError structured fields, SQLState method, and error formatting","kind":"HOW","packages":["pkg:golang/github.com/jackc/pgx/v5@v5.10.0"],"schemaVersion":1,"symbols":["pgconn.PgError"]},"contractCommand":["go","test","./..."],"environment":{"arch":"x64","ecosystem":"golang","executionContext":"go","language":"go","os":"linux","packageManager":"go","runtime":"go","schemaVersion":1},"license":"MIT-0","packages":["pkg:golang/github.com/jackc/pgx/v5@v5.10.0"],"schemaVersion":1,"symbols":["pgconn.PgError"],"verifierAdapter":"golang@1"}

module example.com/pgerrortest

go 1.26.5

require github.com/jackc/pgx/v5 v5.10.0

require (
	github.com/jackc/pgpassfile v1.0.0 // indirect
	github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 // indirect
	golang.org/x/text v0.29.0 // indirect
)

github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c=
github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsIM=
github.com/jackc/pgpassfile v1.0.0/go.mod h1:CEx0iS5ambNFdcRtxPj5JhEz+xB6uRky5eyVu/W2HEg=
github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 h1:iCEnooe7UlwOQYpKFhBabPMi4aNAfoODPEFNiAnClxo=
github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM=
github.com/jackc/pgx/v5 v5.10.0 h1:VhSvgU2jSli8o3AqIEOTJr7rZwAEUVo4E4XhR94Zfr0=
github.com/jackc/pgx/v5 v5.10.0/go.mod h1:mal1tBGAFfLHvZzaYh77YS/eC6IX9OWbRV1QIIM0Jn4=
github.com/jackc/puddle/v2 v2.2.2 h1:PR8nw+E/1w0GLuRFSmiioY6UooMp6KJv0/61nB7icHo=
github.com/jackc/puddle/v2 v2.2.2/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4=
github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM=
github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4=
github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI=
github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg=
github.com/stretchr/testify v1.11.1 h1:7s2iGBzp5EwR7/aIZr8ao5+dra3wiQyKjjFuvgVKu7U=
github.com/stretchr/testify v1.11.1/go.mod h1:wZwfW3scLgRK+23gO65QZefKpKQRnfz6sD981Nm4B6U=
golang.org/x/sync v0.17.0 h1:l60nONMj9l5drqw6jlhIELNv9I0A4OFgRsG9k2oT9Ug=
golang.org/x/sync v0.17.0/go.mod h1:9KTHXmSnoGruLpwFjVSX0lNNA75CykiMECbovNTZqGI=
golang.org/x/text v0.29.0 h1:1neNs90w9YzJ9BocxfsQNHKuAT4pkghyXc4nhZ6sJvk=
golang.org/x/text v0.29.0/go.mod h1:7MhJOA9CD2qZyOKYazxdYMF85OwPdEr9jTtBpO7ydH4=
gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0=
gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA=
gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=

package pgerrortest

import (
	"errors"
	"fmt"
	"testing"

	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgproto3"
)

func TestPgErrorFormattingAndFields(t *testing.T) {
	pgErr := &pgconn.PgError{
		Severity:       "ERROR",
		Code:           "23505",
		Message:        "duplicate key value violates unique constraint \"users_pkey\"",
		Detail:         "Key (id)=(42) already exists.",
		Hint:           "Use a different ID.",
		SchemaName:     "public",
		TableName:      "users",
		ConstraintName: "users_pkey",
	}

	// Error() produces "Severity: Message (SQLSTATE Code)" without embedding Detail, Hint, or ConstraintName
	expectedErrString := "ERROR: duplicate key value violates unique constraint \"users_pkey\" (SQLSTATE 23505)"
	if pgErr.Error() != expectedErrString {
		t.Fatalf("unexpected Error() output: got %q, want %q", pgErr.Error(), expectedErrString)
	}

	// SQLState() returns the 5-character SQL error code matching Code
	if pgErr.SQLState() != "23505" {
		t.Fatalf("unexpected SQLState(): got %q, want %q", pgErr.SQLState(), "23505")
	}

	// Structured diagnostic fields remain accessible on the struct
	if pgErr.Detail != "Key (id)=(42) already exists." {
		t.Fatalf("unexpected Detail: got %q", pgErr.Detail)
	}
	if pgErr.ConstraintName != "users_pkey" {
		t.Fatalf("unexpected ConstraintName: got %q", pgErr.ConstraintName)
	}
	if pgErr.TableName != "users" {
		t.Fatalf("unexpected TableName: got %q", pgErr.TableName)
	}
}

func TestPgErrorUnwrappingWithErrorsAs(t *testing.T) {
	pgErr := &pgconn.PgError{
		Severity: "ERROR",
		Code:     "42P01",
		Message:  "relation \"nonexistent_table\" does not exist",
	}

	wrappedErr := fmt.Errorf("database query failed: %w", pgErr)

	var extractedErr *pgconn.PgError
	if !errors.As(wrappedErr, &extractedErr) {
		t.Fatalf("errors.As failed to extract *pgconn.PgError from wrapped error")
	}

	if extractedErr.Code != "42P01" {
		t.Fatalf("extracted error has wrong Code: got %q, want %q", extractedErr.Code, "42P01")
	}
	if extractedErr.SQLState() != "42P01" {
		t.Fatalf("extracted error SQLState() mismatch: got %q, want %q", extractedErr.SQLState(), "42P01")
	}
}

func TestErrorResponseToPgError(t *testing.T) {
	wireMsg := &pgproto3.ErrorResponse{
		Severity:            "ERROR",
		SeverityUnlocalized: "ERROR",
		Code:                "23505",
		Message:             "duplicate key value violates unique constraint",
		Detail:              "Key (email)=(user@example.com) already exists.",
		Hint:                "Choose another email.",
		SchemaName:          "public",
		TableName:           "accounts",
		ConstraintName:      "accounts_email_key",
		File:                "nbtinsert.c",
		Line:                673,
		Routine:             "_bt_check_unique",
	}

	pgErr := pgconn.ErrorResponseToPgError(wireMsg)
	if pgErr == nil {
		t.Fatal("ErrorResponseToPgError returned nil")
	}

	if pgErr.Code != "23505" || pgErr.SQLState() != "23505" {
		t.Fatalf("unexpected code in converted PgError: got %q", pgErr.Code)
	}
	if pgErr.Detail != "Key (email)=(user@example.com) already exists." {
		t.Fatalf("unexpected detail: got %q", pgErr.Detail)
	}
	if pgErr.TableName != "accounts" || pgErr.ConstraintName != "accounts_email_key" {
		t.Fatalf("unexpected table/constraint metadata: table=%q, constraint=%q", pgErr.TableName, pgErr.ConstraintName)
	}
	if pgErr.Line != 673 || pgErr.Routine != "_bt_check_unique" {
		t.Fatalf("unexpected source location: line=%d, routine=%q", pgErr.Line, pgErr.Routine)
	}
}