Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

parameter not accepted #2148

Open
prr123 opened this issue Oct 12, 2024 · 2 comments
Open

parameter not accepted #2148

prr123 opened this issue Oct 12, 2024 · 2 comments
Labels

Comments

@prr123
Copy link

prr123 commented Oct 12, 2024

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.
@prr123 prr123 added the bug label Oct 12, 2024
@jackc
Copy link
Owner

jackc commented Oct 12, 2024

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
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

3 participants