parameter not accepted

[prr123]

Describe the bug A clear and concise description of what the bug is. I submitted a query "select * from $1;" and executed con.Query(ctx, query, "table") Received the error message: ERROR: syntax error at or near "$1" (SQLSTATE 42601)

I repeated the exercise with the query: "select * from table;" executed con.Query(ctx, query) no error message

From the documentation it is not clear to me whether this is an error or a feature limitation.

To Reproduce Steps to reproduce the behavior: create a simple table with columns "first" and "last". Insert some dummy data and retrieve the data as outlined above

If possible, please provide runnable example such as:

package main

import (
	"context"
	"log"
	"os"

	"github.com/jackc/pgx/v5"
)

func main() {
	conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close(context.Background())

	// Your code here...
    var user_id int
    var first, last string
    query := "select user_id, first, last from $1;"

    rows, err := dbcon.Query(ctx, query, "person")
    if err != nil {
        fmt.Printf("error -- query failed: %v\n", err)
        os.Exit(1)
    }
    defer rows.Close()

}

Please run your example with the race detector enabled. For example, go run -race main.go or go test -race.

Expected behavior A clear and concise description of what you expected to happen.

Actual behavior A clear and concise description of what actually happened.

Version

• Go: $ go version -> [e.g. go version go1.18.3 darwin/amd64] 1.22.5
• PostgreSQL: $ psql --no-psqlrc --tuples-only -c 'select version()' -> [e.g. PostgreSQL 14.4 on x86_64-apple-darwin21.5.0, compiled by Apple clang version 13.1.6 (clang-1316.0.21.2.5), 64-bit] pg 16.04
• pgx: $ grep 'github.com/jackc/pgx/v[0-9]' go.mod -> [e.g. v4.16.1] github.com/jackc/pgx/v5 v5.4.1 Additional context Add any other context about the problem here.

[jackc]

That's a PostgreSQL issue. Placeholders cannot be used for table names. Try this in psql and you will also get an error.

prepare s as select user_id, first, last from $1;

[lucioreyli]

You can resolve this changing the query (string) instead trying to use as placeholder

// That is!
query := fmt.Sprintf("SELECT count(1) FROM %s", "users")

// insert that query here
if r, err := s.db.Query(context.Background(), query); err != nil {
    return err;
} 

var result uint32
r.Next()
r.Scan(&result)
fmt.Println("total users: ", result)

You can do the same for check if a row_id is in array, just changing the query string,like that:

ids := []int{1, 5, 10, 76}
str := []string{}

for index, _ := range ids {
	str = append(str, fmt.Sprintf("$%d", index+1))
}

fmt.Println(str)                    // [$1, $2, $3, $4]
fmt.Println(strings.Join(str, ",")) // $1,$2,$3,$4