How to fix ERROR: ambiguous_parameter in PostgreSQL

-- Before (ambiguous - PostgreSQL cannot determine $1 type)
SELECT * FROM users WHERE status = $1;

-- After (explicit cast)
SELECT * FROM users WHERE status = $1::text;
-- or
SELECT * FROM users WHERE status = CAST($1 AS text);

-- Before (ambiguous - different types in UNION)
SELECT $1 AS value
UNION
SELECT $2 AS value;

-- After (explicit matching types)
SELECT $1::text AS value
UNION
SELECT $2::text AS value;

-- Or better: use explicit column types
SELECT CAST($1 AS text) AS value
UNION
SELECT CAST($2 AS text) AS value;

-- Before (parameter type inferred, may be ambiguous)
PREPARE my_query AS
SELECT * FROM users WHERE id = $1;

-- After (explicit parameter type)
PREPARE my_query (integer) AS
SELECT * FROM users WHERE id = $1;

-- Multiple parameters with types
PREPARE complex_query (text, integer, date) AS
SELECT * FROM orders
WHERE customer_name = $1
AND order_id = $2
AND order_date >= $3;

-- Before (ambiguous - which overload to use?)
SELECT CONCAT($1, $2);

-- After (explicit casting)
SELECT CONCAT($1::text, $2::text);

-- Or use :: on the function result
SELECT (CONCAT($1, $2))::text;

SELECT proname, pronargs, proargtypes
FROM pg_proc
WHERE proname = 'your_function_name'
ORDER BY pronargs;

-- Before (type ambiguous when $1 is NULL)
SELECT * FROM users WHERE name = COALESCE($1, name);

-- After (explicit type for NULL)
SELECT * FROM users WHERE name = COALESCE($1::text, name);

-- Alternative: use CAST
SELECT * FROM users WHERE name = COALESCE(CAST($1 AS text), name);

// Node.js (pg library) - explicit type hints may help
const query = 'SELECT * FROM users WHERE id = $1::integer AND status = $2::text';
const values = [userId, 'active'];
await client.query(query, values);

// With type specification in some drivers
const query = {
  text: 'SELECT * FROM users WHERE id = $1 AND status = $2',
  values: [userId, 'active'],
  // Some drivers allow type hints
};

-- Test the corrected query
EXECUTE my_query('test_value');

-- Or prepare and execute
PREPARE test_query (text) AS SELECT * FROM users WHERE name = $1;
EXECUTE test_query('John');
DEALLOCATE test_query;