"cannot convert 1 to Text"

[mtekmir]

Hi,

I am trying to update several rows with a single query. Query works fine when I run it against the db but fails when I use placeholders.

func (r saleRepository) SyncSoldProducts(db sales.Execer, pp []*sales.SyncedProduct) error {
	pHolders := make([]string, 0, len(pp))
	values := make([]interface{}, 0, len(pp)*3)
	for i, p := range pp {
		pHolders = append(pHolders, fmt.Sprintf("($%d, $%d, $%d)", i*3+1, i*3+2, i*3+3))
		values = append(values, p.ID, p.SaleID, p.Synced) // <- Ids here are just ints
	}
	
	stmt := fmt.Sprintf(`
		UPDATE sold_products AS sp SET synced = v.synced::BOOLEAN
		FROM (VALUES %s) as v(id, sid, synced)
		WHERE sp."productId" = v.id::NUMERIC
		AND sp."saleId" = v.sid::NUMERIC
	`, strings.Join(pHolders, ", "))
	
	_, err := db.Exec(stmt, values...)
	if err != nil {
		return err
	}
	return nil
}

Without typecasting I get this error: ERROR: operator does not exist: bigint = text (SQLSTATE 42883)
When I print the query and the variables, I get these:

Query:

UPDATE sold_products AS sp SET synced = v.synced::BOOLEAN
FROM (VALUES ($1, $2, $3), ($4, $5, $6), ($7, $8, $9)) as v(id, sid, synced)
WHERE sp."productId" = v.id::NUMERIC
AND sp."saleId" = v.sid::NUMERIC

Values slice:

[1 1 true 2 1 true 3 1 true 4 1 true 5 1 true 6 1 true 7 1 true 8 1 true 9 1 true 10 1 true]

The error I'm getting: cannot convert 1 to Text

I am using pgx with database/sql.
Is this related to the pgx driver?

Thanks

[jackc]

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.

[mtekmir]

Adding type info to the placeholders fixed it. Thanks a lot.

[ysheng26]

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.

How can one configure pgx to use simple protocols?

[jackc]

Set PreferSimpleProtocol to true in your ConnConfig.

https://pkg.go.dev/github.com/jackc/pgx/v4?tab=doc#ConnConfig

[ysheng26]

Set PreferSimpleProtocol to true in your ConnConfig.

https://pkg.go.dev/github.com/jackc/pgx/v4?tab=doc#ConnConfig

I'm using pgx with database sql, is there a way to pass in this config? seems like this config is meant to be used when using pgx directly

[jackc]

I'm using pgx with database sql, is there a way to pass in this config?

See the first few paragraphs of the docs for stdlib: https://pkg.go.dev/github.com/jackc/pgx/v4@v4.8.1/stdlib?tab=doc

[ysheng26]

I'm using pgx with database sql, is there a way to pass in this config?

See the first few paragraphs of the docs for stdlib: https://pkg.go.dev/github.com/jackc/pgx/v4@v4.8.1/stdlib?tab=doc

Got it :) thanks!

[mmitevski]

How can I configure pgx to use the simple protocol from the connection string to Pgpool?

My config string is "host=localhost database=test user=postgres password=test"

[jimenez]

Hello @jackc ,
Before switching to pgx I was using github.com/lib/pq and working with queries like this: SELECT * WHERE fields ->> '_version' = $1, where in the go code, the type of $1 is int.
Even after setting PreferSimpleProtocol to true, I still get an error, it seems that the json to integer conversion fails, changing the query to SELECT * WHERE fields -> '_version' = $1 works since it does not convert '_version' to text.
Is there a way to have pgx have the same behavior as pq for json operators?

On a side note, changing the query to SELECT * WHERE fields ->> '_version' = $1::text is not working either, it seems I have to type cast in the go code the type of $1 to string, any idea why?

[jackc]

There shouldn't be anything special about json operators.

Can you provide a executable code sample?

[jimenez]

@jackc here is an example of a non working query, here we're connecting to postgres to a users table that has a fields column of type jsonb that was created as follows:

CREATE TABLE users (
    id text PRIMARY KEY,
    fields jsonb DEFAULT '{}'::jsonb,
);

As mentioned in the code output shown in the gists comment, with or without setting PreferSimpleProtocol we get an error.

https://gist.github.com/jimenez/48a52c4ddad16636869f7b78adf2e527

[jackc]

I was able to narrow it to an even smaller case.

package main

import (
	"context"
	"database/sql"
	"fmt"
	"os"

	"github.com/jackc/pgx/v4"
	_ "github.com/jackc/pgx/v4/stdlib"
)

func main() {
	doStdlib()
	doNative()
}

func doStdlib() {
	db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "stdlib: Unable to connect to database: %v\n", err)
		return
	}
	defer db.Close()

	var id string
	err = db.QueryRow("SELECT $1::text", 2).Scan(&id)
	if err != nil {
		fmt.Fprintf(os.Stderr, "stdlib: QueryRow failed: %v\n", err)
		return
	}

	fmt.Println("stdlib:", id)
}

func doNative() {
	conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "native: Unable to connect to database: %v\n", err)
		return
	}
	defer conn.Close(context.Background())

	var id string
	err = conn.QueryRow(context.Background(), "SELECT $1::text", 2).Scan(&id)
	if err != nil {
		fmt.Fprintf(os.Stderr, "native: QueryRow failed: %v\n", err)
		return
	}

	fmt.Println("native:", id)
}

jack@glados ~/dev/pgx_issues/pgx-798 ±master⚡ » go run main.go
stdlib: QueryRow failed: cannot convert 2 to Text
native: QueryRow failed: cannot convert 2 to Text

However, simple protocol works for me.

jack@glados ~/dev/pgx_issues/pgx-798 ±master⚡ » DATABASE_URL=prefer_simple_protocol=true go run main.go
stdlib: 2
native: 2

The problem is the SQL parameter is a string and the argument is an int. pgx does not automatically make that conversion.

If you are always using an int in that query you could probably use something like:

 SELECT * WHERE fields ->> '_version' = ($1::int)::text

This pgx behavior could be changed. pgtype.Text.Set would need to recognize the additional types and convert them to string.

e.g.

	case int:
		*dst = Text{String: fmt.Sprint(value), Status: Present}

But I'm not sure if that's a good idea or not. Where should the line be drawn between convenience and strict typing. I'm pretty sure it is a bad idea to automatically convert everything to text. That could easily end up with someone silently losing data (e.g. by storing a pointer address instead of the contents). So defaulting to fmt.Sprint(value) is out as far as I'm concerned. But then that means specifying every allowed automatic conversion. Tedious. Obvious to add support for int. But what about *int. Then what about type mynum int. Then *mynum and so on. I try to handle those cases in the actual number types but it is a bit messy.

Then there's also the question of if this should be symmetrical. If an int param can automatically be encoded into text, should a text result automatically be parsed into an int?

[jimenez]

@jackc thanks for your explanation and looking into it. I understand that Pgx doesn't do the automatic type conversion (like Pq) so looks like I need to update my queries or my code, I just have one question regarding:
SELECT * WHERE fields ->> '_version' = ($1::int)::text
Why do I need the double casting instead of just having:
SELECT * WHERE fields ->> '_version' = $1::text
This last one currently works when using simple protocol, is the double casting the only way to make the query work without using simple protocol?

[jackc]

The double cast ($1::int)::text means that the argument is an int but once PostgreSQL has received the int to convert it to text. Without the double cast it would expect text directly which was the original problem.