PostgreSQL error 42P02 occurs when a prepared statement references a parameter placeholder like $1 but no actual value is supplied. Fix by providing all bind variables or removing unused placeholders.
PostgreSQL uses parameter placeholders ($1, $2, etc.) in prepared statements to represent values that will be provided at query execution time. The "Undefined parameter" error (error code 42P02) occurs when PostgreSQL encounters a reference to a parameter placeholder in your query but you haven't supplied the corresponding value when executing the statement. This commonly happens with the first parameter ($1), which is why you may see "there is no parameter $1" errors. Unlike some databases that use ? as placeholders, PostgreSQL's numbered placeholders require explicit values for each referenced number.
Count how many parameter placeholders ($1, $2, etc.) appear in your SQL query. Verify that you're supplying exactly that many values when executing.
Example of missing parameter:
-- This prepared statement expects 1 parameter:
PREPARE user_query AS SELECT * FROM users WHERE id = $1;
-- Wrong - no parameter value provided:
EXECUTE user_query;
-- Correct - parameter value provided:
EXECUTE user_query(123);Ensure every variable bound to a parameter placeholder is properly initialized. In Node.js, undefined values can cause this error even if the count matches.
Example with node-postgres:
const userId = undefined; // WRONG - will cause error
const query = "SELECT * FROM users WHERE id = $1";
await client.query(query, [userId]); // ERROR: undefined parameter
// Correct:
const userId = 42; // or null if that's intentional
const query = "SELECT * FROM users WHERE id = $1";
await client.query(query, [userId]); // WorksSometimes PostgreSQL cannot determine the data type from context alone, especially with NULL comparisons or complex expressions. Cast the parameter explicitly.
Example:
-- May cause error if $1 type is ambiguous:
PREPARE check_value AS SELECT * FROM data WHERE value IS $1;
-- Use explicit casting:
PREPARE check_value AS SELECT * FROM data WHERE value IS $1::boolean;Parameters can only be used for literal values in SELECT, INSERT, UPDATE, DELETE, and VALUES statements. You cannot use them for table names, column names, or SQL keywords.
Wrong:
-- ERROR: cannot use $1 for identifiers
PREPARE bad_query AS SELECT * FROM $1;
PREPARE bad_query AS SELECT $1 FROM users;Correct:
-- Use parameters only for values
PREPARE good_query AS SELECT * FROM users WHERE id = $1;If you're analyzing a prepared statement with EXPLAIN, you must supply parameter values. Alternatively, use PostgreSQL 16+ which supports EXPLAIN (GENERIC_PLAN).
Example:
-- Without values - will error:
EXPLAIN SELECT * FROM users WHERE id = $1;
-- With values - works:
EXPLAIN SELECT * FROM users WHERE id = 123;
-- PostgreSQL 16+ - generic plan (no values needed):
EXPLAIN (GENERIC_PLAN) SELECT * FROM users WHERE id = $1;Parameter type inference is a PostgreSQL feature where the database automatically determines parameter types from context. In ambiguous cases like "WHERE value IS NULL" or "WHERE value = ANY($1)", PostgreSQL may not be able to infer the type and will throw this error. In these situations, explicit type casting (e.g., $1::integer) is required. For monitoring tools that capture queries from pg_stat_statements, parameter values are intentionally omitted for security and performance reasons, which is why tools analyzing those queries must handle parameterized forms differently. Some tools now support PostgreSQL 16's GENERIC_PLAN feature for more accurate query analysis without needing actual parameter values.
insufficient privilege to bypass row security
How to fix "insufficient privilege to bypass row security" in PostgreSQL
HV004: fdw_invalid_data_type
How to fix "HV004: fdw_invalid_data_type" in PostgreSQL
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL