Gorm POSTGRES How to check if error type is DUPLICATE KEY

@jinzhu

OLD Ticket: https://github.com/go-gorm/gorm/issues/4037

I did not find any solution for postgres yet. I searched everywhere but still there is no way i found to check the duplicate key for postgres. @jinzhu Can you provide the PSQL example like you did for the Mysql in old ticket. Thanks

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: spiside

This stackoverflow answer should provide some details on how errors are commonly caught using Postgres error codes. For your problem specifically, I think you can do the following:

const (
    UniqueViolationErr = pq.ErrorCode("23505")
)

if err != nil && errors.Is(err, UniqueViolationErr) {
    fmt.Println("Duplicate key found.")
}

Here's a list of all the SQLSTATE error codes that are used in Postgres. You'll see that 23505 is unique violation error.

Comment From: khanakia

@spiside Your code is not working and giving error please check. FYI: I tried creating some custom errors already in the past with Error Interface those also did not work.

So can you please give some solid solution to these issue. Thanks

Comment From: spiside

I was hoping Is() might do the heavy lifting here but this is how I've defined a function that checks postgres error codes:

const (
    UniqueViolationErr = pq.ErrorCode("23505")
)

func IsErrorCode(err error, errcode pq.ErrorCode) bool {
        if pgerr, ok := err.(*pq.Error); ok {
                return pgerr.Code == errcode
        }
        return false
}

if err != nil && IsErrorCode(err, UniqueViolationErr) {
    fmt.Println("Duplicate key found.")
}

IsErrorCode() casts the error to a pq.Error, and if it is, it will check the error code to see if it matches.

Comment From: khanakia

@spiside

Still returning false

err = db.Save(&item).Error
fmt.Println(IsErrorCode(err, UniqueViolationErr)) // false

1. Also i tried the stackoverflow example and assertion is always false

err := db.Save(&item).Error
err, ok := err.(*pq.Error)
fmt.Println(ok) // false

1. Tried with Custom Error Interface

type PsqlError struct {
    Message string
}

func (me *PsqlError) Error() string {
    return "Some Error"
}

err = db.Save(&item).Error
var perr PsqlError
fmt.Println(errors.Is(err, &perr)) // false

FYI: I did unmarshall the error OBJECT to JSON this is what i see but still none of the above code works

{
  "Severity": "ERROR",
  "Code": "23505",
  "Message": "duplicate key value violates unique constraint \"items_slug_key\"",
  "Detail": "Key (slug)=(test1) already exists.",
  "Hint": "",
  "Position": 0,
  "InternalPosition": 0,
  "InternalQuery": "",
  "Where": "",
  "SchemaName": "public",
  "TableName": "items",
  "ColumnName": "",
  "DataTypeName": "",
  "ConstraintName": "items_slug_key",
  "File": "nbtinsert.c",
  "Line": 656,
  "Routine": "_bt_check_unique"
}

Comment From: spiside

That's odd, because the pq.Error struct defined here: https://pkg.go.dev/github.com/lib/pq#Error is the same as that struct. What do you get for:

var pqErr *pq.Error
if ok := errors.As(err, pqErr); ok && err.Code == UniqueViolationErr {
   fmt.Println("Worked!")
}

Comment From: khanakia

@spiside

It gives the error

But anyway i figured it out i used the fmt.Println(reflect.TypeOf(err)) and it returns *pgconn.PgError it seems goorm uses pgconn package to connect to the Postgres

Then i used below code and it worked 💯

var perr *pgconn.PgError
errors.As(err, &perr)
fmt.Println(perr.Code) // 23505

Comment From: khanakia

@spiside

I am using gorm Datatypes package and i just verified it's using the pgconn package

Comment From: khanakia

@spiside Thank for your great support along the way. I really appreciate it by heart. 👍

Comment From: akishek22

Hey all, would it make more sense if gorm returned unique key violations as a standard gorm error?

Comment From: khanakia

@akishek22 What do you mean by that ?

Comment From: akishek22

@khanakia referencing your image below. For example, I want to keep my gorm db implementation separated from Postgres (flexibility to change DBs). However, when handling unique key errors, it's impossible to know it's a unique key error without knowing it's a Postgres unique key error.

I was thinking of a general "gorm.ErrUniqueKeyViolation" like you referenced in your question. If it's not a good idea, just curious why not.

Comment From: khanakia

@akishek22 those errors are out of scope from the gorm packages as those errors are returned by native drivers itself such as mysql, pq etc.

Comment From: caioaao

those errors are out of scope from the gorm packages as those errors are returned by native drivers itself such as mysql, pq etc.

@khanakia I'd agree if we didn't have the struct tags for unique indexes. seems weird to support writing it but not checking violations

Comment From: udaraliyanage

This worked for me

                var duplicateEntryError = &pgconn.PgError{Code: "23505"}
        if errors.As(err, &duplicateEntryError) {
            return ItemAlreadyExistError
        }

23505 code represent duplicate entry error

Comment From: VerasThiago

Here is how I use

func IsNotFoundError(err error) bool {
    return err == gorm.ErrRecordNotFound
}

func IsDuplicatedKeyError(err error) bool {
    var perr *pgconn.PgError
    if errors.As(err, &perr) {
        return perr.Code == DUPLICATED_KEY
    }
    return false
}

func HandleDuplicateError(err error) error {
    if err == nil {
        return err
    }

    if IsDuplicatedKeyError(err) {
        return GenericError{
            Code:    STATUS_BAD_REQUEST,
            Err:     err,
            Message: "Data already used",
        }
    }

    return err
}

func HandleDataNotFoundError(err error, dataName string) error {
    if err == nil {
        return err
    }

    if IsNotFoundError(err) {
        return GenericError{
            Code:    STATUS_NOT_FOUND,
            Err:     err,
            Message: fmt.Sprintf("%+v not found", dataName),
        }
    }

    return err
}

Example of usage:

func (p *PostgresRepository) DeleteUser(userID string) error {
    err := p.db.Where("id = ?", userID).Delete(&models.User{}).Error
    return errors.HandleDataNotFoundError(err, USER_DATA_NAME)
}

func (p *PostgresRepository) CreateUser(user *models.User) error {
    err := p.db.Create(user).Error
    return errors.HandleDuplicateError(err)
}

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: azmiadhani

I can't assert the gorm error to*pgconn.PgError. errors.As(err, &pgErr) keep returning false. I wonder if anybody can help me with this one. Here is my code: I'm using: 1. golang 1.19 2. gorm.io/gorm v1.24.33 3. github.com/jackc/pgconn v1.13.0

Comment From: azmiadhani

I've solved my previous problem. I get this to work by installing github.com/jackc/pgx instead of github.com/jackc/pgconn. I'm now using *pgconn.PgError from github.com/jackc/pgx/v5/pgconn import. See the image below. Hope it helps for anyone that got the same problem with mine.

Comment From: d2jvkpn

This problem occurs with 'gorm.io/driver/postgres v1.5.0' (gorm.io/gorm v1.25.1). To make the type assertion work as expected, downgrade the version of gorm.io/driver/postgres to 1.4.5.

Comment From: hijklmno

This problem occurs with 'gorm.io/driver/postgres v1.5.0' (gorm.io/gorm v1.25.1). To make the type assertion work as expected, downgrade the version of gorm.io/driver/postgres to 1.4.5.

this also happens with gorm.io/driver/postgres v1.4.8. i ran the following which fixed our unit tests that checks against postgres code: go get gorm.io/driver/postgres@v1.4.5

Comment From: rhzs

Since the error code is only representing unique violation. Checking code alone is not sufficient to determine the primary key violation. You need to check the error message for PRIMARY key violation error.

Comment From: ennnnzo

I've solved my previous problem. I get this to work by installing github.com/jackc/pgx instead of github.com/jackc/pgconn. I'm now using *pgconn.PgError from github.com/jackc/pgx/v5/pgconn import. See the image below. Hope it helps for anyone that got the same problem with mine.

Thanks so much for your comment!! It helped me a lot !!

Comment From: AuroraTea

For who come after:

var pgErr *pgconn.PgError
if errors.As(err, &pgErr) {
    if pgErr.Code == "23505" { 
    // Or use `pgerrcode.UniqueViolation` for better readability
    // Need `go get github.com/jackc/pgerrcode`
    }
}

Or convenient but couldn't know which unique constraint it is. 👇

DB, err = gorm.Open(
    postgres.Open(""),
    &gorm.Config{
        TranslateError: true,
    },
)

if errors.Is(err, gorm.ErrDuplicatedKey) {

}