< Prev: Logging
Up: Basics

Basics: Checking for specific SQL errors

Sometimes when a query fails, you may want to check if the error was due to a Foreign Key constraint violation, or a Unique Key constraint violation, etc. This is how you do it.
Foreign Key Violation
// Postgres
_, err := sq.InsertInto(tbl).
    Column(tbl.A, tbl.B, /* ... */).
    Values("a", "b", /* ...*/).
    Exec(DB, 0)
if err != nil {
    var pqerr *pq.Error
    if errors.As(err, &pqerr) {
        // "23503" is foreign_key_violation in
        // https://www.postgresql.org/docs/current/errcodes-appendix.html
        if pqerr.Code == "23503" {
            // Foreign Key Violation
        }
    }
}

// MySQL
_, err := sq.InsertInto(tbl).
    Column(tbl.A, tbl.B, /* ... */).
    Values("a", "b", /* ...*/).
    Exec(DB, 0)
if err != nil {
    var myerr *mysql.MySQLError
    if errors.As(err, &myerr) {
        // 1216 is ER_NO_REFERENCED_ROW in
        // https://dev.mysql.com/doc/refman/8.0/en/server-error-reference.html
        if myerr.Number == 1216 {
            // Foreign Key Violation
        }
    }
}
Unique Key Violation
// Postgres
_, err := sq.InsertInto(tbl).
    Column(tbl.A, tbl.B, /* ... */).
    Values("a", "b", /* ...*/).
    Exec(DB, 0)
if err != nil {
    var pqerr *pq.Error
    if errors.As(err, &pqerr) {
        // "23505" is unique_violation in
        // https://www.postgresql.org/docs/current/errcodes-appendix.html
        if pqerr.Code == "23505" {
            // Unique Key Violation
        }
    }
}

// MySQL
_, err := sq.InsertInto(tbl).
    Column(tbl.A, tbl.B, /* ... */).
    Values("a", "b", /* ...*/).
    Exec(DB, 0)
if err != nil {
    var myerr *mysql.MySQLError
    if errors.As(err, &myerr) {
        // 1062 is ER_DUP_ENTRY in
        // https://dev.mysql.com/doc/refman/8.0/en/server-error-reference.html
        if myerr.Number == 1062 {
            // Unique Key Violation
        }
    }
}
If you want to check for more errors, just check for the specific error code at https://www.postgresql.org/docs/current/errcodes-appendix.html or https://dev.mysql.com/doc/refman/8.0/en/server-error-reference.html and follow the template here.
< Prev: Logging
Up: Basics