gorm: POSTGRES How to check if error type is DUPLICATE KEY

@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

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.

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.

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

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

@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.

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.

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.

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”)

}

}

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

This worked for me

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

23505 code represent duplicate entry error

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

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