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.