I'm new to golang and pgx and I'm running into an issue when I try to run a simple query. I have the following table in postgres.

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    phone_number TEXT NOT NULL UNIQUE, 
);

When I try to run the following query:

func sampleFunc(db dbClient){
    number := " 111111111"
    rows, err := db.Query(context.Background(), "SELECT user_id FROM users WHERE phone_number=$1::TEXT;", number)
    if err != nil {
        log.Println(err)
        return false, err
    }
}

I get the following error: cannot convert [ 111111111] to Text.

EDIT 03/05/2022

I should note that I'm wrapping the pgxpool.Pool in my own struct, and for some reason there's actually no issue when I use the pgxpool directly.

type dbClient interface {
    Query(ctx context.Context, query string, args ...interface{}) (pgx.Rows, error)
}

type SQLClient struct {
    Conn *pgxpool.Pool
}

func (db *SQLClient) Query(ctx context.Context, query string, args ...interface{}) (pgx.Rows, error) {
    return db.Conn.Query(ctx, query, args)
}

I'm assuming that then something is happening with the type information, but still can't figure out how to go about fixing it.

When I print the args type info in SQLClient.Query, by using fmt.Println(reflect.TypeOf(args)), I get the following: []interface {}

CodePudding user response：

You forgot to add ... to the args argument passed to db.Conn.Query().

func (db *SQLClient) Query(ctx context.Context, query string, args ...interface{}) (pgx.Rows, error) {
    return db.Conn.Query(ctx, query, args...)
}

The spec.

CodePudding user response：

I saw the same problem here

I think this is caused by the combination of pgx internally using prepared statements and PostgreSQL not being able to determine the type of the placeholders. The simplest solution is to include type information with your placeholders. e.g. $1::int. Alternatively, you could configure pgx to use the simple protocol instead of prepared statements.