How to fix ERROR: could not determine data type of parameter $1 in PostgreSQL

The most direct fix is to explicitly cast the parameter to the expected data type using the :: operator.

```sql
-- INCORRECT: PostgreSQL cannot infer type
PREPARE my_plan AS SELECT * FROM my_table WHERE column = $1;
EXECUTE my_plan(NULL);
-- ERROR: could not determine data type of parameter $1

-- CORRECT: Explicitly cast to the expected type
PREPARE my_plan AS SELECT * FROM my_table WHERE column = $1::text;
EXECUTE my_plan(NULL);

-- Also works for IS NULL comparisons:
SELECT * FROM my_table WHERE $1::timestamp IS NULL;

-- Cast to the target column's type:
SELECT * FROM users WHERE email = $1::text;
SELECT * FROM events WHERE created_at = $1::timestamp;
SELECT * FROM scores WHERE value = $1::integer;

If you're binding NULL values from application code, specify the type to the database driver.

// JDBC Example: Using setNull with type information
PreparedStatement ps = connection.prepareStatement("SELECT * FROM table WHERE column = ?");

// INCORRECT: Driver doesn't know the expected type
ps.setNull(1, Types.NULL);  // WRONG

// CORRECT: Specify the expected SQL type
ps.setNull(1, Types.VARCHAR);  // String column
ps.setNull(1, Types.TIMESTAMP);  // Timestamp column
ps.setNull(1, Types.INTEGER);  // Integer column

// Or better, let driver infer from ParameterMetaData:
ParameterMetaData pmeta = ps.getParameterMetaData();
ps.setNull(1, pmeta.getParameterType(1));

// Node.js + pg library
const { Client } = require('pg');
const client = new Client();

// INCORRECT: No type information
await client.query('SELECT * FROM table WHERE column = $1', [null]);

// CORRECT: Use explicit casting in query
await client.query('SELECT * FROM table WHERE column = $1::text', [null]);

Comparisons with IS NULL require explicit type information because NULL alone has no type.

```sql
-- INCORRECT: No type context for $1
SELECT * FROM my_table WHERE $1 IS NULL;
-- ERROR: could not determine data type of parameter $1

-- CORRECT: Cast parameter to expected type
SELECT * FROM my_table WHERE $1::timestamp IS NULL;
SELECT * FROM my_table WHERE $1::text IS NULL;

-- Alternative: Compare to the column instead
SELECT * FROM my_table WHERE my_column = $1 OR ($1 IS NULL AND my_column IS NULL);

-- For CASE expressions:
SELECT CASE
WHEN $1::timestamp IS NOT NULL THEN 'has_date'
ELSE 'no_date'
END;

If you define parameters but don't use them, PostgreSQL cannot determine their types.

-- INCORRECT: Parameter $2 is never used
PREPARE query AS SELECT * FROM users WHERE id = $1;
-- If you execute with 2 parameters, error occurs

-- CORRECT: Use all parameters or don't bind them
PREPARE query AS SELECT * FROM users WHERE id = $1 AND email = $2;
EXECUTE query(5, '[email protected]');

-- Or remove the unused parameter from the application code
user_id = params[0];  // Only use first parameter

Double-check your parameterized queries in the application to ensure every bound parameter has a corresponding placeholder in the SQL.

When using parameters in UNION queries, ensure columns have compatible types.

```sql
-- INCORRECT: Parameter type ambiguous in UNION
SELECT $1 FROM table1
UNION
SELECT name FROM table2;
-- Type of $1 is unclear

-- CORRECT: Cast parameter to match union column types
SELECT $1::text
FROM table1
UNION
SELECT name::text FROM table2;

-- Or ensure parameter matches the common type:
SELECT $1::integer as id
FROM table1
UNION
SELECT user_id::integer FROM users;

Ensure that parameter numbers ($1, $2, etc.) are sequential and match your bound values.

-- INCORRECT: Parameter $2 is missing
PREPARE query AS SELECT * FROM table WHERE col1 = $1 AND col3 = $3;
EXECUTE query('a', 'b', 'c');  -- $2 is missing in the query

-- CORRECT: Parameters are sequential
PREPARE query AS SELECT * FROM table WHERE col1 = $1 AND col2 = $2 AND col3 = $3;
EXECUTE query('a', 'b', 'c');

-- Or use only the parameters you need:
PREPARE query AS SELECT * FROM table WHERE col1 = $1 AND col3 = $2;
EXECUTE query('a', 'c');  -- Only 2 values

Use database tools like psql to test your prepared statements before running them in production.

When possible, compare parameters to actual columns to give PostgreSQL type information.

-- WEAK: Type inference relies on parameter context
WHERE $1 = some_column

-- STRONGER: PostgreSQL knows the type from the column
WHERE some_column = $1::text

-- Best practice for comparison:
WHERE column_name = $1  -- PostgreSQL infers type from column_name

-- For INSERT/UPDATE:
INSERT INTO users (id, email) VALUES ($1::integer, $2::text);
UPDATE users SET email = $1::text WHERE id = $2::integer;

Always cast parameters in INSERT/UPDATE statements to ensure the types match the table schema, even if PostgreSQL might infer them.

Some PostgreSQL drivers allow specifying parameter types in the connection or query.

// Node.js: psqlite or pg-promise
const db = pgp();
db.result('SELECT * FROM table WHERE col = $1::text', [null]);

// Python: psycopg2
import psycopg2
conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()
cur.execute("SELECT * FROM table WHERE col = %s::text", (None,))

// Ruby: pg gem
conn = PG.connect("dbname=test")
conn.exec_params("SELECT * FROM table WHERE col = $1::text", [nil])

Consult your driver's documentation for parameter type hints. Some drivers support inline type annotations, others rely on explicit casting in the SQL.