Strange error, casting changes the parameter

[galileo-pkm]

This works (as expected)

ff=# PREPARE test1 (int, SMALLINT) AS
ff-# SELECT s.id, s.sum, s.capacity, COALESCE((s.data->$2::text->>'amount')::numeric, 0) AS amount 
ff-# FROM storage s JOIN storage_items si ON (s.container_id = si.building_id) 
ff-# JOIN storage_type st ON (s.type_id = st.id) 
ff-# WHERE st.name = 'building' AND s.village_id = $1 AND si.item_id = $2;
PREPARE
ff=# 
ff=# EXECUTE test1(1, 3001);
 id | sum  | capacity | amount 
----+------+----------+--------
  3 | 3000 |   500000 |   3000
(1 row)

Same query in go:

    query := `
        SELECT s.id, s.sum, s.capacity, COALESCE((s.data->$2::text->>'amount')::numeric, 0) AS amount 
        FROM storage s JOIN storage_items si ON (s.container_id = si.building_id) 
        JOIN storage_type st ON (s.type_id = st.id) 
        WHERE st.name = 'building' AND s.village_id = $1 AND si.item_id = $2;
`

    rows, err := db.Query(query, vID, iID)

Gives this error:

<error>
data: <*github.com/lib/pq.Error>(0xc0001a1320)
: <github.com/lib/pq.Error>

Severity: "ERROR"
Code: "42883"
Message: "operator does not exist: smallint = text"
Detail: ""
Hint: "No operator matches the given name and argument types. You might need to add explicit type casts."
Position: "289"
InternalPosition: ""
InternalQuery: ""
Where: ""
Schema: ""
Table: ""
Column: ""
DataTypeName: ""
Constraint: ""
File: "parse_oper.c"
Line: "722"
Routine: "op_error"

If I change the last part to: "AND si.item_id = $2::integer" or to: AND si.item_id = $3 and run a query like this: db.Query(query, vID, iID, iID) The error is gone. vID is int32, iID is int16 if that matters.

As if the $2 is permanently cast into a string (text)? Anyone has any ideas what might cause this?

This is with /lib/pq v1.1.1 and postgresql 11.3

Sorry for the verbatim SQL, didn't have the time to construct a proper test.

[cbandy]

@galileo-pkm Yes, in your plain SQL you tell PostgreSQL the data type of the parameters: PREPARE test1 (int, SMALLINT). To emulate the way this works from the driver, change this to PREPARE test1 (unknown, unknown) or simply PREPARE test1.

As you've found, adding casts is the way to address this.

[galileo-pkm]

So the driver always runs prepared statements with "unknown" type, it does not matter that it knows the parameter type? I guess I just presumed that it passes the type. Maybe an update to the docs is a good idea?