How to fix ERROR: Parameter $N does not exist in PostgreSQL

SELECT * FROM users WHERE id = $1 AND status = $2;

-- This query references $1 but the WHERE clause doesn't have $2
SELECT * FROM users WHERE id = $1;

-- Query has $1, $2, $3 but you'll only provide 2 values
SELECT * FROM users WHERE id = $1 AND username = $2 AND email = $3;

// CORRECT - Parameters array provided with matching values
const result = await client.query(
  'SELECT * FROM users WHERE id = $1 AND status = $2',
  [userId, 'active']  // Must provide 2 values for $1 and $2
);

// WRONG - Parameters not provided
const result = await client.query(
  'SELECT * FROM users WHERE id = $1 AND status = $2'
  // Missing parameters array - will cause "Parameter $1 does not exist"
);

// WRONG - Undefined or null array
let params = null;
const result = await client.query(
  'SELECT * FROM users WHERE id = $1',
  params  // Will fail because params is null
);

# CORRECT
cursor.execute(
  'SELECT * FROM users WHERE id = %s AND status = %s',
  (user_id, 'active')  # Tuple with matching values
)

# WRONG - Missing tuple
cursor.execute(
  'SELECT * FROM users WHERE id = %s AND status = %s'
  # No parameters - causes error
)

// CORRECT
row := conn.QueryRow(ctx, "SELECT * FROM users WHERE id = $1 AND status = $2", userId, "active")

// WRONG - Not passing all required arguments
row := conn.QueryRow(ctx, "SELECT * FROM users WHERE id = $1 AND status = $2")
// Missing userId and "active" arguments

// SAFE - Parameterized query
const result = await client.query(
  'SELECT * FROM users WHERE username = $1',
  [username]
);

// UNSAFE - String concatenation (SQL injection risk)
const result = await client.query(
  `SELECT * FROM users WHERE username = '${username}'`
);

# SAFE
cursor.execute('SELECT * FROM users WHERE username = %s', (username,))

# UNSAFE
cursor.execute(f'SELECT * FROM users WHERE username = \'{username}\'')

// SAFE
row := conn.QueryRow(ctx, "SELECT * FROM users WHERE username = $1", username)

// UNSAFE
query := fmt.Sprintf("SELECT * FROM users WHERE username = '%s'", username)
row := conn.QueryRow(ctx, query)

const { Client } = require('pg');
const client = new Client({
  connectionString: 'postgres://...',
  statement_timeout: 30000,
});
// PgBouncer should be configured with 'query' mode or 'transaction' mode

[databases]
mydb = host=localhost dbname=mydb port=5432

[pgbouncer]
pool_mode = session  # Use session mode, not query or transaction

jdbc:postgresql://localhost:5432/dbname?prepareThreshold=0

const query = 'INSERT INTO users (name, email, age) VALUES ($1, $2, $3)';
const params = ['Alice', '[email protected]', 30];
//              $1      $2                    $3
await client.query(query, params);

const query = 'INSERT INTO users (name, email, age) VALUES ($1, $2, $3)';
const params = ['[email protected]', 'Alice', 30];  // email and name swapped!
//              $1 expects name    $2 expects email  $3 expects age
await client.query(query, params);  // Will insert wrong data

const query = 'INSERT INTO users (name, email, age) VALUES ($1, $2, $3)';
const params = ['Alice', '[email protected]'];  // Missing $3 (age)
//              $1      $2
await client.query(query, params);  // Error: Parameter $3 does not exist

const name = 'Alice';
const email = '[email protected]';
const age = 30;

const query = 'INSERT INTO users (name, email, age) VALUES ($1, $2, $3)';
const params = [name, email, age];
await client.query(query, params);

let paramIndex = 1;
const filters = [];
const params = [];

if (userId) {
  filters.push(`id = $${paramIndex++}`);
  params.push(userId);
}
if (status) {
  filters.push(`status = $${paramIndex++}`);
  params.push(status);
}

const query = `SELECT * FROM users WHERE ${filters.join(' AND ')}`;
// Result: "SELECT * FROM users WHERE id = $1 AND status = $2"
await client.query(query, params);

const params = [userId, status, email];
const query = `
  SELECT * FROM users
  WHERE id = $1 AND status = $2 AND role = $4
  -- Missing $3, jumped to $4!
`;
await client.query(query, params);  // Error: Parameter $4 does not exist

const query = 'SELECT * FROM users WHERE id = $1 AND status = $2';
const params = [userId, 'active'];

console.log('Query:', query);
console.log('Params:', params);

try {
  const result = await client.query(query, params);
} catch (err) {
  console.error('Query failed:', err.message);
  console.error('Expected params: 2, Provided:', params.length);
}

query = 'SELECT * FROM users WHERE id = %s AND status = %s'
params = (user_id, 'active')

print(f'Query: {query}')
print(f'Params: {params}')

try:
  cursor.execute(query, params)
except Exception as e:
  print(f'Query failed: {e}')
  print(f'Expected params: 2, Provided: {len(params)}')