How do I interpret PostgreSQL error messages from within Go?

Question

How can I parse SQL constraint errors, in particular resulting from constraints such as UNIQUE, FOREIGN KEY, NOT NULL, ON DELETE RESTRICT within Go?

e.g. Insert a new user that may have the same email which is defined as a UNIQUE field in the database. Parse constraint error in Go, return error to browser client.

Answer 1

For information about how to interpret errors from github.com/lib/pq, see http://godoc.org/github.com/lib/pq#Error.

Here is what I do:

// ShowError sends an appropriate error message.
func ShowError(w http.ResponseWriter, r *http.Request, err error) {
    switch e := err.(type) {
    case *pq.Error:
        switch e.Code {
        case "23502":
            // not-null constraint violation
            http.Error(w, fmt.Sprint("Some required data was left out:\n\n", e.Message), http.StatusForbidden)
            return

        case "23503":
            // foreign key violation
            switch r.Method {
            case "DELETE":
                http.Error(w, fmt.Sprint("This record can’t be deleted because another record refers to it:\n\n", e.Detail), http.StatusForbidden)
                return
            }

        case "23505":
            // unique constraint violation
            http.Error(w, fmt.Sprint("This record contains duplicated data that conflicts with what is already in the database:\n\n", e.Detail), http.StatusForbidden)
            return

        case "23514":
            // check constraint violation
            http.Error(w, fmt.Sprint("This record contains inconsistent or out-of-range data:\n\n", e.Message), http.StatusForbidden)
            return

        default:
            msg := e.Message
            if d := e.Detail; d != "" {
                msg += "\n\n" + d
            }
            if h := e.Hint; h != "" {
                msg += "\n\n" + h
            }
            http.Error(w, msg, http.StatusInternalServerError)
            return
        }

    case *strconv.NumError:
        http.Error(w, fmt.Sprintf(`"%s" is not a valid number.`, e.Num), http.StatusBadRequest)
        return

    default:
        switch err {
        case sql.ErrNoRows:
            http.NotFound(w, r)
            return
        }
    }

    http.Error(w, err.Error(), http.StatusInternalServerError)
}