0

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 {}

sgonzalez
  • 741
  • 6
  • 20
  • https://imgur.com/a/0tvADtP. The error does not seem to me to be coming from that piece of code that you have shared. Given the `number` variable's value it is going to already be formatted as `text` anyway, there's not even any need to try to type cast it (unless, of course, its value is very different from what you've shown). Consider providing a [mcve]. – mkopriva Mar 05 '22 at 08:07
  • Thanks @mkopriva, you are right that there is no issue when directly using the pgx connection. I wrap the connection in my own struct, and actually just pass an interface around the application where the db is needed. This is issue is reproducible when passing args through an extra method. I added more details to the post. – sgonzalez Mar 05 '22 at 21:55
  • 2
    You forgot the `...`, i.e. `return db.Conn.Query(ctx, query, args...)`. That's why you get `[` and `]` around the number in the error message. You are passing `args` which is a slice, not the phone number string. – mkopriva Mar 05 '22 at 21:56
  • Yes you are totally right, this is the issue. I appreciate the pointer. If you want to put it as an answer, I'll mark it as the solution. – sgonzalez Mar 05 '22 at 22:02

2 Answers2

3

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.

mkopriva
  • 35,176
  • 4
  • 57
  • 71
0

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.

Nermin
  • 1
  • 2