inconsistent types deduced

[aeon3k]

Describe the bug
I try to use twice the $4 argument but it doesn't work in this specific case

`
INSERT INTO public.users (username, email, provider, client_uid, client_id)
VALUES ($1, $2, $3, $4, (select public.clients.id from public.clients where public.clients.uid = $4 limit 1))
RETURNING id, uid`

err := r.pool.QueryRow(context.Background(), q, username, email, "own", "7Rp9WhF3PdrkhY1uZbXj8").Scan(&RetID, &RetUID)

In the DB schema public.clients.uid and client_uid are of the same type : character varying

Expected behavior
I expect to have the same type inference both times (varchar I presume)

Actual behavior
ERROR: inconsistent types deduced for parameter $4 at character 119 (SQLSTATE 42P08)
DETAIL: text versus character varying

Version
PostgreSQL 14.15 (Postgres.app) on aarch64-apple-darwin20.6.0, compiled by Apple clang version 12.0.5 (clang-1205.0.22.9), 64-bit
github.com/jackc/pgx/v5 v5.7.2
go version go1.23.3 darwin/amd64

Additional context

I've tried with a prepared statement in PG Admin and the same query works fine.

I found 2 solutions :

• If I use WHERE uid = $5 LIMIT 1)) it works fine (I double the same argument)
• If I use WHERE uid = $4::varchar LIMIT 1)) it works fine

In another similar insert query where I use twice the same $ argument it works fine. In this case it's an int64 (bigint in DB).

Thanks in advance for your help.
Cheers

[jackc]

I've tried with a prepared statement in PG Admin and the same query works fine.

I'm not sure what PgAdmin does, but can you try it directly in psql?

e.g.

prepare s as insert into ...

That's basically all pgx is doing.

[boindil]

Same problem here @jackc

Simple reproduction

Working

CREATE TABLE test (
    id character varying(10) primary key,
    counter int NOT NULL
);

PREPARE insert_test (character varying(10)) AS
INSERT INTO test (id, counter) VALUES ($1, (
						SELECT COALESCE(MAX(counter), 0) + 1
                    	  FROM test
                    	 WHERE id = $1
					));
EXECUTE insert_test('foo');
EXECUTE insert_test('bar');
EXECUTE insert_test('foobar');

Not working

sqlQuery := `INSERT INTO test (id, counter) VALUES ($1, (
						SELECT COALESCE(MAX(counter), 0) + 1
                    	  FROM test
                    	 WHERE id = $1
					));`

	_, err = (*tx).Exec(
		context.Background(),
		sqlQuery,
		"testid", // $1
	)
	if err != nil {
		return err
	}

[jackc]

@boindil Your raw SQL example and your pgx example are not exactly the same.

PREPARE insert_test (character varying(10)) AS

This is telling PostgreSQL the type of the argument. If you give the same information to pgx it will work. You can do that by specifying the type of the $ arguments in the SQL query.

sqlQuery := `INSERT INTO test (id, counter) VALUES ($1::varchar(10), (