SQL query with EXCEPT doesn't work in Golang-CodePudding

I can't understand why my INSERT query doesn't properly work with parameters, but works fine with direct values in it.

Golang, Postgres.

This code works fine:

fmt.Printf("connected to %s\n", ec.DBName)
q := `
INSERT INTO some_table (name, num) VALUES ('sample',20)
EXCEPT
SELECT name, num FROM some_table;`
_, err = db.Query(q)
if err != nil {
  fmt.Println(err)
}

But this one raises an error:

fmt.Printf("connected to %s\n", ec.DBName)
q := `
INSERT INTO some_table (name, num) VALUES ($1,$2)
EXCEPT
SELECT name, num FROM some_table;`
_, err = db.Query(q, "sample", 20)
if err != nil {
  fmt.Println(err)
}

The error is "pq: EXCEPT types text and integer cannot be matched"

CodePudding user response：

You can send both values as string and try to cast them to the desired types via sql. Something like this -

q := `
INSERT INTO some_table (name, num) 
VALUES (cast($1 as text), cast($2 as int)) 
EXCEPT 
SELECT name, num FROM some_table;`
_, err = db.Query(q, "sample", "20")
if err != nil {
  fmt.Println(err)
}

CodePudding user response：

There are PostGreSQL keywords that you have to be careful not to use out of context. Not sure if your quoted example is the exact code you are running: name shouldn't be a reserved word but desc is.

So to be safe, as a general for all SQL queries for user tables & columns, I would wrap them with quotes to avoid any keyword collisions:

q := `
INSERT INTO "some_table" ("name", "num") VALUES ($1,$2)
EXCEPT
SELECT "name", "num" FROM "some_table";`