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 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
SSL error: certificate does not match host name
SSL error: certificate does not match host name in PostgreSQL
No SSL connection
No SSL connection to PostgreSQL