cfatt10 cfatt10 - 6 months ago 29
SQL Question

Comparing field to placeholder - PostgreSQL

I am using Go to execute a query on a PostgreSQL DB. Note that in the DB

id
is type
bigint
.

db.Exec("UPDATE tags SET association_count = association_count - 1 WHERE id=?;", id)


When I run this code, I get a SQL error

operator does not exist: bigint =?


From what I can tell this is caused by a typing mismatch between the
id
and the
?
. I've tried casting with
::
but it throws an error, and haven't found anything about this. Interestingly, if there is a space, like
id = ?
it throws a general syntax error. Any idea how to cast or work around this?

Answer

Postgres uses $# for placeholders postgres docs

When creating the prepared statement, refer to parameters by position, using $1, $2, etc. A corresponding list of parameter data types can optionally be specified. When a parameter's data type is not specified or is declared as unknown, the type is inferred from the context in which the parameter is used (if possible). When executing the statement, specify the actual values for these parameters in the EXECUTE statement. Refer to EXECUTE for more information about that.

so try

result, err := db.Exec(`
    UPDATE tags
    SET association_count = association_count - 1
    WHERE id=$1;`, id
)

Here is what result looks like

Comments