// PostgreSQL error reference

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
Category
Syntax Error or Access Rule Violation
Error message
could not determine data type of parameter $1

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 NULL checks, 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

  1. Cast the placeholder to the intended type.

    SELECT * FROM orders
    WHERE ($1::int IS NULL OR customer_id = $1::int);
  2. 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

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.

// fewer errors, faster fixes

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.