pgx: Error codes helper function

Coming on this thread was a life saver. Thanks Jack for all your hard work!

Check out https://github.com/jackc/pgerrcode/

1. The error is *PgError not PgError.
2. Use error unwrapping rather than a direct type assertion.

See https://github.com/jackc/pgx/wiki/Error-Handling

Those functions can return a *pgconn.PgError.

Any error reported by PostgreSQL will be returned. So you can totally get PostgreSQL errors from QueryRow.

But sql.ErrNoRows is distinctly a database/sql concept. As far as PostgreSQL is concerned a query returning zero rows is not an error.

pgerrcode.NoData exists because it is listed on https://www.postgresql.org/docs/current/errcodes-appendix.html. But as far as I can tell from a quick look at the PostgreSQL source code it is never actually used.

It works for me with database/sql.

package main

import (
	"database/sql"
	"errors"
	"log"
	"os"

	"github.com/jackc/pgconn"
	"github.com/jackc/pgerrcode"
	_ "github.com/jackc/pgx/v4/stdlib"
)

func main() {
	db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatalln(err)
	}
	defer db.Close()

	var n int
	err = db.QueryRow("select 1/0").Scan(&n)
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) {
			switch pgErr.Code {
			case pgerrcode.DivisionByZero:
				log.Println("correctly matched division by zero error")
				os.Exit(0)
			}
		}
	}
	log.Fatalln("did not match error", err)
}

Output:

jack@glados ~/dev/pgx_issues/pgx-474 ±master⚡ » go run main.go
2020/07/13 07:43:08 correctly matched division by zero error

@jackc how could one get hold of pgError when using pgxpool exec? That seems to return only an error string message – https://pkg.go.dev/github.com/jackc/pgx/v4/pgxpool#Conn.Exec ?

Exec returns a string. Wrapping doesn’t seem to work either.

if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) {
			fmt.Println(pgErr.Message) // => syntax error at end of input
			fmt.Println(pgErr.Code)    // => 42601
		}
     }

Any error reported by PostgreSQL will be returned. So you can totally get PostgreSQL errors from QueryRow.

But sql.ErrNoRows is distinctly a database/sql concept. As far as PostgreSQL is concerned a query returning zero rows is not an error.

pgerrcode.NoData exists because it is listed on https://www.postgresql.org/docs/current/errcodes-appendix.html. But as far as I can tell from a quick look at the PostgreSQL source code it is never actually used.

You nailed it for me. Thank you so much!

I think I still prefer the type assertion style, so I’m going to pass on this for now.

Yeah, but the thing is err, ok := err.(pgx.PgError); ok && err.Code == pgerr.UniqueViolation is a lot of typing, and there’s some logic to it that writer can make a mistake in.

Essentially, the thing I’m proposing is one step forward after putting err, ok := err.(pgx.PgError); ok && err.Code == code in a function. I.e.:

func IsErrCode(err error, code string) bool {
    pgerr, ok := err.(pgx.PgError)
    return ok && pgerr.Code == code
}

Again, the pattern I repetitively observe is this one err, ok := err.(pgx.PgError); ok && err.Code == pgerr.UniqueViolation, and after screwing up with it (yep, maybe it’s just me, but the I sometimes do mistakes in the things even simple like this) I decided to do something with it, and that’s how I extracted that function. One immediate benefit that I got was much less typing (with autocompletion it’s really just a few keystrokes) - and that quickly became even more valuable that it prevents errors. I admit I’m lazy. 😃

I don’t think there’s more to it, it’s up to decide whether it worth adding or not. If you like it I’ll create a PR.

Actually, the implementation can be a bit more concise:

func ErrorCode(err error) string {
	pgerr, ok := err.(pgx.PgError)
	if !ok {
		return ""
	}
	return pgerr.Code
}

The bool is not really meaningful, as in the most used case in my code if often just check for code equality with some non-empty string. Returning bool is not useful then, because just the code is enough.