PostgreSQL 42P18: could not determine data type of parameter
PostgreSQL couldn't infer a bound parameter's type from where it is used, for example SELECT $1 or $1 IS NULL. Add an explicit cast.
- SQLSTATE
42P18- Name
indeterminate_datatype
What it means
When a statement with placeholders is prepared, PostgreSQL works out each parameter's type from its context, such as the column it is compared with. If the context gives no clue (SELECT $1, $1 IS NULL, $1 || 'x' with an unknown type, a parameter passed to an overloaded function) and the driver sent the parameter as untyped, preparing fails with 42P18 (indeterminate_datatype).
A cast on the placeholder tells PostgreSQL the type and fixes it.
Common causes
- A parameter used only in the select list or in
IS NULLchecks, such as optional filters written as($1 IS NULL OR col = $1). - A parameter passed to a function with several overloads.
- A driver that sends all parameters with an unspecified type.
How to fix it
-
Cast the placeholder to the intended type.
SELECT * FROM orders WHERE ($1::int IS NULL OR customer_id = $1::int);
-
Or have the driver send typed parameters; most drivers have a way to bind a value with an explicit type.
Reproduce it safely
PREPARE p AS SELECT $1; -- ERROR: could not determine data type of parameter $1
Related errors
More syntax error or access rule violation errors
42601syntax error at or near42501permission denied42846cannot coerce42803column must appear in the GROUP BY clause42830invalid foreign key42703column does not exist42P01relation does not exist42704undefined object
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.