Your Question
I have unique index key and when i try to insert duplicate record i get this error ERROR: duplicate key value violates unique constraint "idx_itemid_partid" (SQLSTATE 23505) but i want to show different message to the frontend based on ERROR TYPE but in gorm i do not see any Duplicate error type please check screenshot. How do i use the validation for duplicate error type
if err != nil && errors.Is(err, gorm.ErrDuplicateKey) { // how do i do this ?
fmt.Println("Duplicate key found.")
}
The document you expected this should be explained
N/A
Expected answer
Should have error type duplicate
Comment From: jinzhu
use mysql as example, check it like:
var mysqlErr *mysql.MySQLError
if errors.As(err, &mysqlErr) && mysqlErr.Number == 1062 {
}
Comment From: FedorLap2006
I think better would be easier to have such common error in predefined errors emitted by gorm.
Comment From: khanakia
@jinzhu How do i do that for postgres it's not working
var psqlErr postgres.PostgreError // ERROR postgres package has not type or method
if errors.As(err, &psqlErr) && psqlErr.Number == 1062 {
}
Comment From: khanakia
@jinzhu Any update on this ? I have check everywhere there is no solution i found for this.
Comment From: GiancarlosIO
👀
Comment From: khanakia
@GiancarlosIO What ??
Comment From: jinagamvasubabu
Currently i am able to solve this issue with a hack (even i expect gorm should solve it in a better way)
import "github.com/jackc/pgconn"
....
....
if strings.Contains(err.(*pgconn.PgError).Message, "duplicate key value violates unique constraint") {}
//even you can change the condition to check on `Code` as well instead of `Message`
Comment From: bitbreakr
@khanakia
Hope it helps:
if results := db.Create(&account); results.Error != nil {
if pgError := results.Error.(*pgconn.PgError); errors.Is(results.Error, pgError) {
var httpStatus int
switch pgError.Code {
case "23505":
httpStatus = 409
}
c.Status(httpStatus)
return nil
}
c.Status(fiber.StatusBadRequest)
return nil
}
Comment From: gozelus
use mysql as example, check it like:
go var mysqlErr mysql.MySQLError if errors.As(err, &mysqlErr) && mysqlErr.Number == 1062 { }
It will panic ：
panic: errors: target must be a non-nil pointer
Comment From: fatsoap
Try this, it works for me!
first you have to defined gorm error struct, your can find the format by json.Marshal(err)
if err := db.AddUser(&r).Error; err != nil {
b, _ := json.Marshal(err)
fmt.Println(string(b))
}
then, marshal the error to json then unmarshal it back
type GormErr struct {
Number int `json:"Number"`
Message string `json:"Message"`
}
if err := db.AddUser(&r).Error; err != nil {
byteErr, _ := json.Marshal(err)
var newError GormErr
json.Unmarshal((byteErr), &newError)
fmt.Println(newError)
}
you can check the error type by newError.Number !
switch newErr.Number{
case 1062:
fmt.Println("Duplicate Key !")
}
if you got duplicate entry error, format may be like this
{
"Number": 1062,
"Message": "Duplicate entry 'XXXXX' for key 'users.XXX'
}
Comment From: hadihammurabi
If you want to check type of error, just do it.
if err := db.Create(&user).Error; err != nil {
if errors.Is(err, gorm.ErrRecordNotFound) {
fmt.Println(err.Error())
}
return
}
Comment From: zesagata
any future plan to create predefined error for this case ?
Comment From: jan-opal
any future plan to create predefined error for this case ?
Also curious about this. Seems like a pretty common error case that developers might want to handle gracefully
Comment From: MRarity
error is an interface, you can get the mysqlErr by type asseration
err := db.Create(&user).Error mysqlErr := err.(*mysql.MySQLError) switch mysqlErr.Number { case 1062: fmt.Println("Duplicate Key") }
Comment From: jan-opal
error is an interface, you can get the mysqlErr by type asseration
Makes sense. FYI, for others that need this for postgres, since gorm uses pgx, you can do the following:
err := db.Create(&user).Error
if err != nil {
var pgErr *pgconn.PgError
if errors.As(err, &pgErr) {
fmt.Println(pgErr.Message)
fmt.Println(pgErr.Code)
}
}
you then can use this to match error codes: https://github.com/jackc/pgerrcode/blob/master/errcode.go
Comment From: ghost
if you use postgresql you can check the error code whether equals to pgerrcode.UniqueViolation. before you check the error code, you should convert error type to *pgconn.PgError first, it works.
Comment From: noldd
Could still use a more universal solution to this than explicitly using psql/mysql error codes.
Comment From: fabian-lapotre
What about doing this kind of check with sqlite driver ?
Comment From: kayvonkhosrowpour
+1 for wanting this feature
Comment From: sudoamin2
Sqlite3
e := errors.New("UNIQUE constraint failed")
if errors.As(err, &e) {}
Comment From: batara666
why are this closed?
Comment From: silvioprog
I created a naive function to handle errors from DB (SQLite/PostgreSQL only), and the unique violation handling part is:
import (
"errors"
"github.com/jackc/pgconn"
"github.com/jackc/pgerrcode"
"github.com/mattn/go-sqlite3"
...
func TranslateErrors(value *gorm.DB) error {
if value.Error != nil {
switch value.Dialector.Name() {
case "sqlite":
if err, ok := value.Error.(sqlite3.Error); ok {
if err.ExtendedCode == sqlite3.ErrConstraintUnique {
return errors.New("record already exists")
}
// other errors handling from sqlite3
}
case "postgres":
if err, ok := value.Error.(*pgconn.PgError); ok {
if err.Code == pgerrcode.UniqueViolation {
return errors.New("record already exists")
}
// other errors handling from pgconn
}
}
}
return value.Error
}
Comment From: HeCorr
@silvioprog you forgot to mention which package you're using to get the error constants.
https://github.com/jackc/pgerrcode
Comment From: silvioprog
Thanks a lot @HeCorr. I added the imports and also updated the function above to a version I'm using that avoids panic.
Comment From: Henelik
+1 for an elegant way to handle this that doesn't break multi-database support for the official options; i.e. a GORM error var. I'll submit a PR if I can get work approval.
Comment From: shvkhzod
I checked the error codes with pq library
err :=db.Create(&user).Error
if err !=nil {
if err.(*pq.Error).Code == "23505" { fmt.Println("Username already exists")
}
}
Comment From: artlevitan
Use this function to check for errors in SQLite 3
func gormErrUniqueViolation(err error) bool {
e := errors.New("UNIQUE constraint failed")
return !errors.Is(err, e)
}
Example after the INSERT request:
// ...
err = db.Create(&data).Error
if err != nil { // Check SQL Errors
if gormErrUniqueViolation(err) { // UNIQUE constraint failed
// ...
return
}
// ...
}
// ...
Comment From: forChin
For Postgres
import (
"github.com/jackc/pgerrcode"
"github.com/jackc/pgx/v5/pgconn"
)
...
_, err := s.db.ExecContext(ctx, query, args...)
if err != nil {
var pgErr *pgconn.PgError
if errors.As(err, &pgErr) && pgErr.Code == pgerrcode.UniqueViolation {
// duplicate key error
}
return err
}
...
Comment From: guillermo-st
Does the TranslateError flag also work when using gorm.Open() with an existing *sql.DB?
TranslateError:true works just fine when instantiating my *gorm.DB with:
connStr := fmt.Sprintf("host=%v port=%v user=%v password=%v dbname=%v sslmode=%v", dbHost, dbPort, dbUser, dbPass, dbName, dbSSL)
db, err := gorm.Open(postgres.Open(connStr), &gorm.Config{TranslateError: true})
But when I try to open a gorm connection with an existing sql connection (specifically during a TestMain() function that includes a dockertest setup), the errors don't get translated (for example, the Unique Key Violation). Here is how I'm opening it in my TestMain():
databaseUrl := fmt.Sprintf("postgres://testpguser:testpgpassword@%s/testpgdb?sslmode=disable", hostPort)
// exponential backoff-retry, because the application in the container might not be ready to accept connections yet
pool.MaxWait = 120 * time.Second
var sqlDB *sql.DB
if err = pool.Retry(func() error {
sqlDB, err = sql.Open("postgres", databaseUrl)
if err != nil {
return err
}
return sqlDB.Ping()
}); err != nil {
log.Fatalf("Could not connect to docker: %s", err)
}
gormDB, err := gorm.Open(postgres.New(postgres.Config{Conn: sqlDB}), &gorm.Config{TranslateError: true})
Comment From: DiPaolo
Just in case if anyone else face with the same issue - here is what works for me (SQLite):
// setup gorm
cfg := &gorm.Config{
TranslateError: true,
}
// ...
// try to create item doesn't meet unique constraint
result := db.Create(&myEntity)
if result.Error == gorm.ErrDuplicatedKey {
// ...
}
Comment From: Yash1256
Just in case if anyone else face with the same issue - here is what works for me (Postgres): By simply using a translator flag as described in the documentation.
gormDB, err := gorm.Open(postgres.New(postgres.Config{
Conn: db,
PreferSimpleProtocol: true,
}), &gorm.Config{
TranslateError: true,
})
Try to create item doesn't meet unique constraint, and it will come in the ifBlock
res := dbconn.Create(&<model_name>)
if res.Error == gorm.ErrDuplicatedKey {
// ...
}
Comment From: 9ssi7
Just in case if anyone else face with the same issue - here is what works for me (Postgres): By simply using a translator flag as described in the documentation.
gormDB, err := gorm.Open(postgres.New(postgres.Config{ Conn: db, PreferSimpleProtocol: true, }), &gorm.Config{ TranslateError: true, })Try to create item doesn't meet unique constraint, and it will come in the ifBlock
res := dbconn.Create(&<model_name>) if res.Error == gorm.ErrDuplicatedKey { // ... }
the solution worked for my state, thanks for idea!
Comment From: o-igor-trentini
I conducted some tests, and the solution I developed worked well for me. Below is the implementation for PostgreSQL:
// IsPostgresDuplicateKeyError checks if the provided err is a duplicate key error generated by PostgreSQL.
//
// The function accepts an err as a parameter and returns a boolean indicating whether the err is a duplicate key error.
// It checks if the err is of type `pgconn.PgError` and compares the error code with `23505`, which is the PostgreSQL
// error code for unique key violations.
//
// https://github.com/go-gorm/gorm/issues/4037
func IsPostgresDuplicateKeyError(err error) bool {
if err == nil {
return false
}
var pgErr *pgconn.PgError
if errors.As(err, &pgErr) {
/*
Since GORM does not directly support checking this error, it is necessary to do a manual verification.
Example of an error generated by Postgres:
{
...
Code: "ERROR_CODE",
Message: "duplicate key value violates unique constraint "CONSTRAINT_NAME"",
Details: "Key (COLUMN_NAME)=(COLUMN_VALUE) already exists.",
...
}
*/
return pgErr.Code == "23505"
}
return false
}
Comment From: dhairya-chai
Hello please reopen the issue as there is still no universal way to check. Checking for each type of database separately defeats the whole purpose of using an orm.