Question 388744 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Golang squirrel query builder build query which does not execute?

I have a code which does unit testing, where I prepare a database for the tests (I do not use sql-go-mock), so I want to insert returning id with a simple sql INSERT INTO, but for some reason pgx errors with panic: ERROR: syntax error at or near ")" (SQLSTATE 42601), but the query does execute in a goland console.

What is wrong here? By the way, the code does not even work without RETURNING id.

Query built by the builder: INSERT INTO collections (name) VALUES (?),(?) RETURNING id

Simplified example below

package main

import (
    "context"
    "fmt"
    sq "github.com/Masterminds/squirrel"
    "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
    dsn := "postgresql://user:password@localhost:5432/merge?sslmode=disable"
    ctx := context.Background()
    pool, err := pgxpool.New(ctx, dsn)
    if err != nil {
        panic(err)
    }

    type a struct {
        ID   uint
        Name string
    }

    as := []a{{
        Name: "asd",
    }, {
        Name: "zxc",
    }}

    stmt := sq.Insert("collections").Columns("name").
        Values(as[0].Name).Values(as[1].Name).
        Suffix("RETURNING id")

    query, args, err := stmt.ToSql()
    if err != nil {
        panic(err)
    }
    err = pool.QueryRow(ctx, query, args).Scan(&as)
    if err != nil {
        panic(err)
    }
    fmt.Printf("%v", as)
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Here is the code, changed according to the @mkopriva's answer

    package main

import (
    "context"
    "fmt"
    sq "github.com/Masterminds/squirrel"
    "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
    dsn := "postgresql://user:password@localhost:5432/merge?sslmode=disable"
    ctx := context.Background()
    pool, err := pgxpool.New(ctx, dsn)
    if err != nil {
        panic(err)
    }
    defer pool.Close()

    var ids []int

    type a struct {
        ID   uint
        Name string
    }

    as := []a{{
        Name: "asd",
    }, {
        Name: "zxc",
    }}

    stmt := sq.Insert("collections").Columns("name").
        Values(as[0].Name).Values(as[1].Name).
        Suffix("RETURNING id").PlaceholderFormat(sq.Dollar)

    query, args, err := stmt.ToSql()
    fmt.Printf("%vn", args)

    rows, err := pool.Query(ctx, query, args...)
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        if err := rows.Scan(&id); err != nil {
            panic(err)
        }
        ids = append(ids, id)
    }

    if err := rows.Err(); err != nil {
        panic(err)
    }

    fmt.Printf("%vn", ids)
}

  2. 0

    The ? is not a valid parameter placeholder in PostgreSQL, instead PostgreSQL uses $N where N is the position of the argument, e.g. $1 for the first argument, $2 for the second, etc.

    • What you’re probably missing is psql := sq.StatementBuilder.PlaceholderFormat(sq.Dollar), and then you should be able use the returned psql builder to build your queries with the correct placeholder format.
    • Alternatively you can also just append the .PlaceholderFormat(sq.Dollar) method call to your sq.Insert("collections")... statement.
    psql := sq.StatementBuilder.PlaceholderFormat(sq.Dollar)

// You use question marks for placeholders...
sql, _, _ := psql.Select("*").From("elephants").Where("name IN (?,?)", "Dumbo", "Verna").ToSql()

/// ...squirrel replaces them using PlaceholderFormat.
sql == "SELECT * FROM elephants WHERE name IN ($1,$2)"


/// You can retrieve id ...
query := sq.Insert("nodes").
    Columns("uuid", "type", "data").
    Values(node.Uuid, node.Type, node.Data).
    Suffix("RETURNING "id"").
    RunWith(m.db).
    PlaceholderFormat(sq.Dollar)

query.QueryRow().Scan(&node.id)

    Example taken from squirrel‘s README.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search