PostgreSQL 42P02 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42P02: undefined parameter

PostgreSQL error code 42P02 occurs when a SQL statement or function references a parameter placeholder (such as $1, $2, $3) that has not been defined or exceeds the number of parameters actually bound at execution time. This error is caught at the parsing stage before any data is accessed, and it most commonly surfaces when working with prepared statements, PL/pgSQL dynamic SQL, or client-side query binding.

---

Top 3 Causes

1. Mismatch Between Declared and Referenced Parameters in PREPARE

The most common cause is declaring fewer parameter types in a PREPARE statement than the number of $N placeholders used in the query body.

-- Wrong: only 1 type declared, but $2 is referenced
PREPARE bad_example (int) AS
  SELECT * FROM orders WHERE user_id = $1 AND product_id = $2;
-- ERROR:  there is no parameter $2

-- Correct: declare all parameter types
PREPARE good_example (int, int) AS
  SELECT * FROM orders WHERE user_id = $1 AND product_id = $2;

EXECUTE good_example(100, 200);
DEALLOCATE good_example;

2. Dynamic SQL in PL/pgSQL with Missing USING Arguments

When using EXECUTE ... USING inside a PL/pgSQL function, the number of values passed in the USING clause must exactly match the highest $N referenced in the SQL string.

-- Wrong: $3 referenced but only 2 values in USING
CREATE OR REPLACE FUNCTION get_user(p_id int, p_name text)
RETURNS SETOF users AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT * FROM users WHERE id = $1 AND name = $2 AND active = $3'
    USING p_id, p_name;
    -- ERROR: there is no parameter $3
END;
$$ LANGUAGE plpgsql;

-- Correct: add the missing parameter
CREATE OR REPLACE FUNCTION get_user(p_id int, p_name text, p_active boolean)
RETURNS SETOF users AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT * FROM users WHERE id = $1 AND name = $2 AND active = $3'
    USING p_id, p_name, p_active;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_user(1, 'Alice', true);

3. Using $N Placeholders in Plain Queries

Developers coming from other database backgrounds sometimes mistakenly use $1-style placeholders in a plain SELECT statement outside of a prepared statement context.

-- Wrong: $1 used in a plain query with no binding context
SELECT * FROM users WHERE id = $1;
-- ERROR:  there is no parameter $1

-- Correct option 1: use a literal value
SELECT * FROM users WHERE id = 42;

-- Correct option 2: use PREPARE/EXECUTE
PREPARE fetch_user (int) AS
  SELECT * FROM users WHERE id = $1;

EXECUTE fetch_user(42);
DEALLOCATE fetch_user;

---

Quick Fix Solutions

• Audit your $N numbers: Make sure they are sequential ($1, $2, $3...) with no gaps, and that the total count matches the declared parameter list or USING clause.
• Use RAISE NOTICE for debugging dynamic SQL strings before executing them:

-- Debug tip: print the SQL string before execution
RAISE NOTICE 'SQL to execute: %', v_sql;

• Prefer format() over string concatenation when building dynamic SQL to keep parameter indexing clean and prevent SQL injection:

-- Safer dynamic SQL construction
v_sql := format(
  'SELECT * FROM %I WHERE id = $1 AND status = $2',
  v_table_name
);
RETURN QUERY EXECUTE v_sql USING p_id, p_status;

---

Prevention Tips

1. Always keep parameter declarations and references in sync.
Treat the parameter type list in PREPARE or the USING clause as a strict contract with your SQL string. Add a code review checkpoint that verifies $N count equals the number of bound values every time a query is modified.

2. Write unit tests for functions using dynamic SQL.
Use pgTAP or a lightweight testing framework to cover dynamic SQL functions with multiple input combinations. Catching 42P02 in a CI pipeline is far cheaper than debugging it in production.

---

Related Errors

Code	Name	Relation
42601	syntax_error	Often co-occurs when placeholders are placed in invalid positions
08P01	protocol_violation	Triggered at the protocol level when client-side parameter count mismatches server declaration
22023	invalid_parameter_value	Parameter exists but the value type or range is invalid — a close cousin of 42P02

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.