PostgreSQL Indeterminate Datatype (SQLSTATE 42P18)

When PostgreSQL cannot determine the data type of a parameter or expression from context, it raises ERROR: could not determine data type of parameter $1 (or similar), with SQLSTATE 42P18 and condition name indeterminate_datatype. This error belongs to error class 42 ("Syntax Error or Access Rule Violation") and is distinct from a type mismatch — PostgreSQL simply has no information from which to infer the type at all.

What This Error Means

SQLSTATE 42P18 falls under PostgreSQL error class 42, which covers syntax errors and access rule violations. Within that class, indeterminate_datatype specifically signals that the type resolution system ran out of context: it could not bind a type to a parameter placeholder or an expression because nothing in the query provides enough clues.

PostgreSQL resolves types through a process called type inference. When you write $1 = 5, PostgreSQL can infer $1 is an integer. When you write $1 IS NULL or use $1 in a context where no typed expression provides a hint — such as SELECT $1 with no surrounding cast, function call, or comparison — the parser has nothing to work from and raises 42P18.

This error occurs at parse/plan time, before any rows are processed, so no data is modified when it is raised. The transaction (if any) remains open and the statement is simply rejected. It is most commonly encountered with prepared statements (both server-side PREPARE and driver-level parameterized queries) and with function bodies that use EXECUTE with untyped parameters.

Common Causes

1. Bare parameter in SELECT with no type context. A prepared statement or PREPARE command uses a parameter in a position where PostgreSQL cannot infer its type, such as SELECT $1 or SELECT $1, $2 with no table columns, functions, or casts to anchor the types.

2. Parameter used only in a IS NULL or IS NOT NULL check. WHERE $1 IS NULL gives PostgreSQL no type information because IS NULL accepts any type — nothing narrows the choice.

3. Untyped literal in a UNION or VALUES clause. A VALUES ($1) or SELECT NULL in a UNION where the corresponding column in the other branch is itself unresolved.

4. Dynamic SQL with EXECUTE in PL/pgSQL using an untyped expression. Inside a PL/pgSQL function, EXECUTE 'SELECT $1' USING some_var may fail if some_var is declared as an untyped placeholder or the expression lacks enough context.

5. ORM or driver sending NULL without a type annotation. Some drivers (particularly older JDBC or Go database/sql drivers) send NULL parameters without a type OID, causing PostgreSQL to receive a completely typeless value.

How to Fix indeterminate_datatype

1. Add an explicit cast to the parameter. The most direct fix is to cast the parameter to its intended type using ::type syntax or the CAST() function:

   -- Instead of:
   PREPARE my_stmt AS SELECT $1;
   
   -- Use:
   PREPARE my_stmt AS SELECT $1::text;
   -- or
   PREPARE my_stmt AS SELECT CAST($1 AS integer);
   

2. Put the parameter in a typed context. Reference a typed column alongside the parameter so PostgreSQL can infer the type by association:

   -- This fails — $1 has no context:
   PREPARE lookup AS SELECT $1;
   
   -- This works — $1 is compared to a typed column:
   PREPARE lookup AS SELECT id, name FROM users WHERE id = $1;
   

3. Use a typed row constructor or function call. Wrapping the parameter in a function that has a known return type resolves the ambiguity:

   -- Forces $1 to be treated as text:
   PREPARE my_stmt AS SELECT length($1);
   

4. Fix driver-level NULL handling. If the error comes from a driver sending a NULL without a type OID, pass an explicit typed null. In JDBC, use setNull(1, Types.VARCHAR) instead of setObject(1, null). In Go's database/sql, use sql.NullString{} or similar typed null wrappers rather than a bare nil.

5. Annotate parameters in PL/pgSQL EXECUTE. When using EXECUTE ... USING in a PL/pgSQL function, ensure variables passed via USING are properly typed:

   CREATE OR REPLACE FUNCTION example(val text) RETURNS text AS $$
   DECLARE
     result text;
   BEGIN
     -- The USING clause passes a typed variable — no ambiguity:
     EXECUTE 'SELECT $1::text' INTO result USING val;
     RETURN result;
   END;
   $$ LANGUAGE plpgsql;
   

6. Provide column type hints in VALUES lists. When building multi-row inserts or CTEs with VALUES, cast at least one value per column to anchor the type:

   -- Ambiguous when used standalone:
   VALUES ($1), ($2)
   
   -- Anchored:
   VALUES ($1::int), ($2::int)
   

Additional Information

• SQLSTATE 42P18 has been part of PostgreSQL's error catalog since at least PostgreSQL 8.x and the behavior has remained consistent through PostgreSQL 16/17.
• Related SQLSTATE codes in class 42 include 42601 (syntax_error), 42703 (undefined_column), 42883 (undefined_function), and 42804 (datatype_mismatch). 42P18 is distinct in that the type is absent, not wrong.
• The error message typically reads: ERROR: could not determine data type of parameter $N where $N is the parameter number. Occasionally you may see could not determine polymorphic type because input has type "unknown" in contexts involving polymorphic functions.
• PostgreSQL's unknown pseudo-type (used for untyped string literals) is related but different — unknown literals are eventually resolved by coercion, while a true indeterminate_datatype error means even that resolution path is unavailable.
• The Go pgx driver and the Python psycopg2/psycopg3 drivers both allow specifying parameter types explicitly when constructing prepared statements, which is the preferred solution in typed-null scenarios.

Frequently Asked Questions

Why does SELECT $1 fail in a prepared statement but SELECT 'hello' works fine? The literal 'hello' has type unknown, which PostgreSQL can resolve to text in most contexts. A parameter $1, however, has no type information at all until the planner sees how it is used. In SELECT $1 with no surrounding context, there is nothing to resolve it against, so PostgreSQL refuses the statement entirely rather than guessing.

The query works fine when run directly but fails when sent as a prepared statement — why? When you run a query directly (in psql or via a simple query protocol message), PostgreSQL sometimes applies extra coercions on the result side. With a prepared statement, the types of all parameters must be fully resolved at prepare time, before any values are bound. The stricter type resolution at prepare time is what exposes the 42P18 error that a one-shot query might mask.

Is this error ever raised outside of prepared statements? Yes, though less commonly. It can appear in PL/pgSQL when using EXECUTE ... USING with expressions that have no type, in COPY commands with certain expressions, or when calling polymorphic functions with arguments that cannot be resolved to a concrete type.

My ORM generates the query — how do I know which parameter is untyped? Enable PostgreSQL logging at log_min_messages = DEBUG2 (or check pg_stat_activity) to capture the exact query text sent by the ORM. The error message identifies which $N parameter is untyped. Then trace back which ORM bind variable maps to that position and add an explicit type annotation or cast at the ORM/driver layer.