Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Go Select In using a list of uuid strings

Tags:

database

psql

go

I have a list of uuid strings that I want to use to filter a query. I can get the query to work if I loop over elements in my list like so:

for i, fileUID := range fileUIDs {
    db.Exec("DELETE FROM files WHERE uid = $1::uuid", fileUID)
}

But I'd like to get it working using the list:

db.Exec("DELETE FROM files WHERE uid IN $1::uuid[]", fileUIDs)

Is this possible? I can't seem to get it working.

I tried the solution in How to execute an IN lookup in SQL using Golang? but I get errors like pq: syntax error at or near "," when using plain ? or pq: syntax error at or near "::" when using ?:uuid. I used the following:

fileUIDArgs := make([]interface{}, len(fileUIDs))
for i, fileUID := range fileUIDs {
    fileUIDArgs[i] = interface{}(fileUID)
}
//also tried using "?::uuid"
myPsql := "DELETE FROM files WHERE uid IN (" + "?" + strings.Repeat(",?", len(uidStrings)-1) + ")"
db.Exec(myPsql, fileUIDArgs...)
like image 332
Charles L. Avatar asked Sep 01 '25 04:09

Charles L.


1 Answers

This is an old question but for the sake of people who will be directed here, if you are using postgres db you can use this easier way:

DELETE FROM files WHERE uid=ANY($1);

$1 is an array of uuids. so your query becomes:

toBeDeleted:= []uuid.UUID{....}
_, err = tx.Exec("UDELETE FROM files WHERE uid=ANY($1);",toBeDeleted)

//or

_, err = tx.Exec("UDELETE FROM files WHERE uid=ANY($1);",pq.Array(toBeDeleted))

either should work for you.

like image 178
Patrick Aboagye Avatar answered Sep 02 '25 17:09

Patrick Aboagye