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