Basics: Checking for specific SQL errors

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
        }
    }
}