inconsistent types deduced for parameter $2

Symptom

Every call of one endpoint fails with HTTP 500; the server log shows:

error: inconsistent types deduced for parameter $2
  code: '42P08'

In psql with PREPARE the same statement gives:

ERROR:  inconsistent types deduced for parameter $2
LINE 3:   SELECT $1, $2
                     ^
DETAIL:  numeric versus bigint

The query looks correct and the values passed are fine; it never reaches execution.

When it happens

A parameterized statement (node-postgres client.query(text, values), any driver using the extended protocol, or PREPARE without types) where the same $n appears in two places that imply different types. The classic shape is a conditional insert:

INSERT INTO grants (operator_id, amount_micro)      -- amount_micro is bigint
SELECT $1, $2
WHERE (SELECT COALESCE(SUM(amount_micro), 0) FROM grants) + $2 <= 1000000000;

• In the SELECT list, $2 is matched to the target column → bigint.
• In SUM(bigint) + $2, SUM over bigint returns numeric, so $2 is deduced as numeric.

Two deductions, two types → 42P08. Tested on PostgreSQL 17.11; the rule is long-standing parser behaviour, not specific to 17.

What does not trigger it (checked): INSERT … SELECT $1, $2 alone (both deduced from the target columns: {uuid,bigint}), and reusing $1 in WHERE NOT EXISTS (… WHERE operator_id = $1) (both uses say uuid). So a statement can work for months and break when someone adds one arithmetic condition.

Cause

node-postgres sends the text with parameter types left unspecified (OID 0), so PostgreSQL infers each parameter's type from context at parse time. It infers from every occurrence; if they disagree it refuses rather than picking one. JavaScript values give no type hint — 10000000 and "10000000" both arrive as text.

Fix

Cast every parameter at every use in INSERT … SELECT (and any statement where a parameter is reused):

INSERT INTO grants (operator_id, amount_micro)
SELECT $1::uuid, $2::bigint
WHERE (SELECT COALESCE(SUM(amount_micro), 0) FROM grants) + $2::bigint <= 1000000000;

await client.query(
  `INSERT INTO grants (operator_id, amount_micro)
   SELECT $1::uuid, $2::bigint
   WHERE (SELECT COALESCE(SUM(amount_micro), 0) FROM grants) + $2::bigint <= 1000000000`,
  [operatorId, 10_000_000],
);

Casting only the first occurrence is not enough if the other occurrence still forces a different type; cast them all to the same type. Alternatively pass each value twice as separate parameters ($2 and $3), each used once.

Verify

Check new SQL before it ships with PREPARE in a throwaway container — it fails at parse time, no data needed beyond the table:

docker run -d --name pgt -e POSTGRES_PASSWORD=x postgres:17-alpine; sleep 5
docker exec -i pgt psql -U postgres <<'SQL'
CREATE TABLE grants (operator_id uuid, amount_micro bigint);
PREPARE p AS INSERT INTO grants (operator_id, amount_micro)
  SELECT $1::uuid, $2::bigint
  WHERE (SELECT COALESCE(SUM(amount_micro),0) FROM grants) + $2::bigint <= 1000000000;
SELECT name, parameter_types FROM pg_prepared_statements;   -- p | {uuid,bigint}
SQL
docker rm -f pgt

With node-postgres 8.23.0 the untyped version threw inconsistent types deduced for parameter $2 | code 42P08, the cast version inserted 1 row.

Notes

• Testing the query in psql with literal values (SELECT '…', 10000000 WHERE … + 10000000) passes — literals are typed differently from parameters. Test with PREPARE, not by pasting values.
• An ORM/query builder that sends typed parameters may not show this; raw pg does.
• A related psql surprise from the same work: psql -tA prints a bare boolean as t, but bool || '' as true — test expectations on concatenated booleans must say true/false.