could not determine data type of parameter

[wj-stack]

Version

1.16.0

What happened?

Statements that cannot parse the column name can be executed, and the type cannot be inferred after changing the name of $2

Relevant log output

# package commcarddb
queries/commcard/commcard.sql:1:1: could not determine data type of parameter $2

Database schema

CREATE TABLE public.solar_commcard_mapping (
 id integer NOT NULL,
 "deviceId" integer NOT NULL,
 version character varying(32) DEFAULT ''::character varying NOT NULL,
 sn character varying(32) DEFAULT ''::character varying NOT NULL,
 "createdAt" timestamp with time zone DEFAULT now(),
 "updatedAt" timestamp with time zone DEFAULT now()
);

SQL queries

This will work, but $2 won't resolve the column name


-- name: InsertMappping :batchexec 
WITH table1 AS
(
 SELECT "version" from solar_commcard_mapping where "deviceId" = $1 order by "updatedAt" desc limit 1
)
INSERT INTO solar_commcard_mapping("deviceId", "version", "sn","updatedAt")
SELECT $1,$2::text,$3,$4 WHERE not exists(SELECT * from table1 where table1."version" = $2::text) or not exists (select * from table1) ;

package commcarddb

queries/commcard/commcard.sql:1:1: could not determine data type of parameter $2

WITH table1 AS
(
 SELECT "version" from solar_commcard_mapping where "deviceId" = $1 order by "updatedAt" desc limit 1
)
INSERT INTO solar_commcard_mapping("deviceId", "version", "sn","updatedAt")
SELECT $1,@version::text,$3,$4 WHERE not exists(SELECT * from table1 where table1."version" = @version::text) or not exists (select * from table1) ;

Configuration

version: "2"
sql:
 - schema: "./schema/"
 queries: "./queries/dumpservice/"
 engine: "postgresql"
 gen:
 go:
 package: "dumpservicedb"
 sql_package: "pgx/v4"
 out: "./sql/queries/dumpservicedb"
 emit_exact_table_names: true
 emit_json_tags: true
 - schema: "./schema/"
 queries: "./queries/mapping/"
 engine: "postgresql"
 gen:
 go:
 package: "mappingdb"
 sql_package: "pgx/v4"
 out: "./sql/queries/mappingdb"
 emit_exact_table_names: true
 emit_json_tags: true
 - schema: "./schema/"
 queries: "./queries/sitemapping/"
 engine: "postgresql"
 gen:
 go:
 package: "sitemappingdb"
 sql_package: "pgx/v4"
 out: "./sql/queries/sitemappingdb"
 emit_exact_table_names: true
 emit_json_tags: true
 - schema: "./schema/"
 queries: "./queries/commcard/"
 engine: "postgresql"
 gen:
 go:
 package: "commcarddb"
 sql_package: "pgx/v4"
 out: "./sql/queries/commcarddb"
 emit_exact_table_names: true
 emit_json_tags: true

Playground URL

https://play.sqlc.dev/p/5fbfef02281fb67691c3cec9b2d084e5fde4329dcef538c7c84013fb2be45663

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[wj-stack]

Sorry, I forgot to change $3 to $2, but after I modified it, it appeared again :batch* commands require parameters

[kyleconroy]

This is fixed in v1.23.0 by enabling the database-backed query analyzer. We added a test case for this issue so it won’t break in the future.

You can play around with the working example on the playground