Insert Ignore query giving error in gorm golang

Issue

I have a list of genres that I am trying to insert into the database. This is to be done just once when the application runs for the very first time. Hence, I am trying to insert ignore but it is giving me some error.

The GenreCategoires struct:

type GenreCategories struct {
    Category string `gorm:"unique"`
}

The query is as follows:

func (a *App) setupGenreCategories() {
    for _, value := range handler.GenreCategorySlice {
        a.DB.Clauses(clause.Insert{Modifier: "ignore"}).Create(&models.GenreCategories{
            Category: value,
        })
        // Alternate approach but with the same errors:
        if a.DB.Model(&models.GenreCategories{}).Where("category = ?", value).RowsAffected == 0 {
            a.DB.Create(&models.GenreCategories{
                Category: value,
            })
        }
    }
}

Here is the error that I am receiving:

near "ignore": syntax error
[0.019ms] [rows:0] INSERT ignore INTO `genre_categories` (`category`) VALUES ("sunsets")

For the alternate approach, the error is as follows:

UNIQUE constraint failed: genre_categories.category
[0.056ms] [rows:0] INSERT INTO `genre_categories` (`category`) VALUES ("sunsets")

Is my syntax wrong or related to gorm v2, I am using gorm v1.22, hope this information is sufficient. Thanks in advance.

Solution

You are using MySQL syntax for Sqlite3, which will obviously cause issues. In sqlite you need to do INSERT OR IGNORE, not INSERT IGNORE, so you most likely simply need to change

a.DB.Clauses(clause.Insert{Modifier: "ignore"})

to

a.DB.Clauses(clause.Insert{Modifier: "or ignore"})

Answered By – dave

Answer Checked By – Terry (GoLangFix Volunteer)

Leave a Reply

Your email address will not be published.