Repository navigation
sqlx.compileNamedQuery() malforms queries with Postgres-style typecasts #650
Description
Suppose you have a table named my_table created using
CREATE TABLE my_table (
id bigint,
name text NOT NULL
);Typecasting to a record type using a Postgres typecast yields a syntax error:
package main
import "github.com/jmoiron/sqlx"
import _ "github.com/lib/pq"
func main() {
db, err := sqlx.Open("postgres", "postgres://username:password@localhost/dbname")
if err != nil {
panic(err)
}
rows, err := db.NamedQuery(`SELECT (1, 'John')::my_table`, make(map[string]interface{}))
if err != nil {
panic(err)
}
defer rows.Close()
}Error received:
err = {error | *github.com/lib/pq.Error}
Severity = {string} "ERROR"
Code = {github.com/lib/pq.ErrorCode} "42601"
Message = {string} "syntax error at or near ":""
Detail = {string} ""
Hint = {string} ""
Position = {string} "19"
InternalPosition = {string} ""
InternalQuery = {string} ""
Where = {string} ""
Schema = {string} ""
Table = {string} ""
Column = {string} ""
DataTypeName = {string} ""
Constraint = {string} ""
File = {string} "scan.l"
Line = {string} "1123"
Routine = {string} "scanner_yyerror"
The query returned by sqlx.compileNamedQuery() is missing a colon:
SELECT (1, 'John'):my_tableThe same query using CAST() instead of :: succeeds:
SELECT CAST((1, 'John') AS my_table)Reacted by Paul Tötterman, ocket8888, aereal, Zhao Puming, Joshua Ma, Svetoslav Stanev, Pavel Rezunenko, RaymondSalim and Nikita
Activity
ptman commentedon Sep 22, 2020
I just ran into the exact same problem with something like
WHERE foo=:foo AND NOW() < (bar - '5 minutes'::interval)nikk-gr commentedon Sep 12, 2024
same problem
nicolabovolato commentedon Oct 4, 2024
Same issue here!