I want to check if record exist and if not exist then i want to insert that record to database using golang

Question

package main

import (
    "database/sql"
    "fmt"

    "github.com/gin-gonic/gin"
)

func main() {

    router := gin.New()
    router.Use(gin.Logger())
    router.Use(gin.Recovery())
    db, err := sql.Open("mysql", "root:password@tcp(gpstest.cksiqniek8yk.ap-south-1.rds.amazonaws.com:3306)/tech")
    if err != nil {
        fmt.Print(err.Error())
    }
    err = db.Ping()
    if err != nil {
        fmt.Print(err.Error())
    }
    rows, err := db.Query("select sum(usercount) as usercount from ( select count(*) as usercount from category where name = 'construction' union all  select count(*) as usercount from sub_category where name = 'construction'  union all  select count(*) as usercount from industry where name = 'construction' ) as usercounts;")

}

Ok, you are checking if it exists, with your `SELECT`. Now test the result, and `INSERT` if it does not exist — Mawg says reinstate Monica, Mar 23 '18 at 11:58
"I want to check if record exist and if not exist then i want to insert that record to database" MySQL supports this more or less out off the box with `INSERT ... SELECT ... WHERE ...` syntax https://dev.mysql.com/doc/refman/5.7/en/insert-select.html — Raymond Nijland, Mar 23 '18 at 12:17
I don't think that does what you think it does; nor what the OP is asking : "With INSERT ... SELECT, you can quickly insert many rows into a table from the result of a SELECT statement". OP wants `INSERT IF NOT EXIST`, which MySql does not support — Mawg says reinstate Monica, Mar 23 '18 at 12:23
trust me @Mawg it's possible with `INSERT ... SELECT ... WHERE ...` i've done it before.. it only the topicstarter would share this table and example data he would already have a MySQL only answer. — Raymond Nijland, Mar 23 '18 at 12:31
If you say so (and you have the rep). I live & learn :-/ https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql? — Mawg says reinstate Monica, Mar 23 '18 at 12:32
@Mawg you can use `NOT EXISTS` in the `WHERE` clause of the `SELECT`, which effectivelly gives you `INSERT IF NOT EXISTS`. See here: https://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table — mkopriva, Mar 23 '18 at 12:34
D'oh!! I wasn't paying attention, sorry :-( I thought is was the old, "update it if it exists, insert it otherwise - in one statement " question. — Mawg says reinstate Monica, Mar 23 '18 at 13:04

score 8 · Answer 1 · answered Mar 23 '18 at 12:20

8

One possible approach would be:

var exists bool
row := db.QueryRow("SELECT EXISTS(SELECT 1 FROM ...)")
if err := row.Scan(&exists); err != nil {
    return err
} else if !exists {
    if err := db.Exec("INSERT ..."); err != nil {
        return err
    }
}

answered Mar 23 '18 at 12:20

mkopriva

35,176
4
57
71

1

What prevents the value from being inserted by another operation between the SELECT and the INSERT? – JimB Mar 23 '18 at 13:10
1

Nothing, while it may be obvious that that would be a required aspect of a real solution, it seemed to me, considering OP's example code, that their question was more about how would one execute the steps, if-not-exists-then-insert, in Go. – mkopriva Mar 23 '18 at 13:15
Fair enough, but since mysql has a way to do this in a single query, that should probably be the answer (making this really unrelated to Go at all) – JimB Mar 23 '18 at 13:20
@mkopriva, it is showing me an error, return err. too many arguments something like that – waseem khan Mar 26 '18 at 17:49
@waseemkhan please update your question with the code that is returning the error, without the code I cannot know what exactly you are doing wrong. – mkopriva Mar 26 '18 at 18:10
@waseemkhan your router.POST handler `func(c *gin.Context)` does not have a return type, and therefore should not return any value. You can instead use an empty `return` statement with no variables after it. Something like this: https://play.golang.org/p/oGjskCheiyZ – mkopriva Mar 27 '18 at 07:20

score 2 · Answer 2 · answered Mar 23 '18 at 12:30

First execute the select statement. Then with rows.Next() check if there is a record on the database. If not, execute the insert query.

rows, err := db.Query("select sum(usercount) as usercount from ( select count(*) as usercount from category where name = 'construction' union all  select count(*) as usercount from sub_category where name = 'construction'  union all  select count(*) as usercount from industry where name = 'construction' ) as usercounts;")
if err != nil {
    log.Fatal(err)
}

if rows.Next() {
    //exists
} else {
    db.Query("INSERT INTO...")
}

Bernd Buffen · Answer 3 · 2018-03-23T14:57:26.020

1

IGNORE is your friend!

You can do it directly with one query if you have a unique index of the field that you want to check with a query like this:

INSERT IGNORE .........;

edited Mar 23 '18 at 14:57

answered Mar 23 '18 at 12:28

Bernd Buffen

14,525
2
24
39

score 0 · Answer 4 · answered Mar 20 '21 at 02:32

I've created a function that can be used check if a user record exists or not, you can repurpose it for other tables


func userWithFieldExists(field string, value string) bool {
    var count int64
    if err := config.DB.Model(&User{}).Select("id").Where(fmt.Sprintf("%s = ?", field), value).Count(&count).Error; err != nil {
        log.Errorf("unable to retrieved user: %v", err)
        return false
    }
    if count > 0 {
        return true
    }
    return false
}

It can be used like so:

userWithFieldExists("email", user.Email)

I want to check if record exist and if not exist then i want to insert that record to database using golang

4 Answers4

Linked

Related