PostgreSQL Ambiguous Function (SQLSTATE 42725)

When PostgreSQL cannot determine which overloaded function to call, it raises:

ERROR:  function <name>(<arg_types>) is not unique
LINE 1: SELECT <name>(...);
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
SQLSTATE: 42725

The condition name is ambiguous_function (SQLSTATE class 42 — Syntax Error or Access Rule Violation). PostgreSQL raises this when a function call matches two or more overloaded candidates equally well after type coercion rules are applied, and the type resolution algorithm cannot select a single winner.

What This Error Means

PostgreSQL supports function overloading: multiple functions can share the same name as long as their argument types differ. When you call a function, PostgreSQL runs a multi-step type resolution algorithm (described in the documentation as "function type resolution") to find the best match among all candidates. It considers exact matches, binary-compatible coercions, and implicit casts, applying a set of tie-breaking rules.

SQLSTATE 42725 fires when the algorithm reaches the end of those rules with more than one candidate still tied. Unlike 42883 (undefined_function), which means no candidate was found at all, 42725 means too many candidates survived the elimination rounds. The query is rejected — no function is called and no side effects occur.

This error belongs to SQLSTATE class 42 (Syntax Error or Access Rule Violation). It is a parse/planning-time error, not a runtime error, so the transaction is not in an aborted state; you can retry with corrected syntax in the same session without issuing a ROLLBACK.

The most common trigger is passing an untyped literal or a NULL to an overloaded function. Untyped literals and bare NULL values have no concrete type during planning, so PostgreSQL cannot use type preference rules to break the tie among candidate signatures.

Common Causes

1. Untyped NULL argument. Calling my_func(NULL) when multiple signatures exist (e.g., my_func(integer) and my_func(text)) leaves PostgreSQL unable to pick one, because NULL has no type.

2. Untyped literal that could cast to multiple types. A bare string literal like '42' can be coerced to integer, bigint, numeric, text, and many other types. If several overloaded signatures accept different numeric or text types with equal preference, the call is ambiguous.

3. Two signatures with argument types that are both implicitly castable from the supplied type. For example, if you call my_func(1) and both my_func(bigint) and my_func(numeric) exist, PostgreSQL may not be able to prefer one implicit cast over the other.

4. Third-party extensions registering conflicting function signatures. Extensions such as PostGIS or custom operator libraries occasionally register functions that overlap with existing ones, causing previously unambiguous calls to become ambiguous after the extension is installed.

5. Schema search path surfacing multiple definitions. If two schemas on the search_path each contain a function with the same name and compatible (but distinct) signatures, both may be candidates.

How to Fix ambiguous_function

1. Add an explicit type cast to disambiguate the argument. This is the most direct fix. Cast the argument to the exact type the intended overload expects:

   -- Ambiguous: NULL has no type
   SELECT my_func(NULL);
   
   -- Fixed: cast NULL to the desired type
   SELECT my_func(NULL::integer);
   SELECT my_func(NULL::text);
   

2. Cast untyped literals to a specific type:

   -- Ambiguous
   SELECT my_func('42');
   
   -- Fixed
   SELECT my_func('42'::integer);
   SELECT my_func('42'::text);
   

3. Use schema-qualified function names to target a specific overload. If the ambiguity arises from two schemas on the search path:

   SELECT public.my_func(42);
   SELECT extensions.my_func(42);
   

4. Drop or rename one of the conflicting overloads if the duplication is unintentional:

   -- List all overloads for a function name
   SELECT p.proname, pg_catalog.pg_get_function_arguments(p.oid) AS args
   FROM pg_catalog.pg_proc p
   JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
   WHERE p.proname = 'my_func'
   ORDER BY n.nspname;
   
   -- Drop the unwanted overload (specify exact arg types)
   DROP FUNCTION my_func(text);
   

5. Reorder or narrow the search_path to exclude the schema containing the unwanted overload:

   SET search_path = public;
   -- or permanently for a role:
   ALTER ROLE myuser SET search_path = public;
   

Additional Information

• SQLSTATE class 42 covers syntax and access-rule errors. Related codes include 42883 (undefined_function — no matching function found) and 42703 (undefined_column). Unlike runtime errors in class 22 (data exceptions), class 42 errors abort only the current statement, not necessarily the entire transaction.
• The function type resolution algorithm is fully documented in the PostgreSQL manual under "Functions — Type Resolution". Understanding the preference ordering (exact match → binary-compatible cast → implicit cast) helps predict when ambiguity will occur.
• This error has been part of PostgreSQL since at least version 7.4 and the behavior is stable across all supported versions (PostgreSQL 12 through 17).
• Most client drivers (psycopg2, JDBC, node-postgres) surface this error verbatim as a database exception with sqlstate / getSQLState() returning "42725". The HINT field ("You might need to add explicit type casts") is included in the server message and is often the most actionable part of the error for end users.
• ORMs that generate dynamic function calls (e.g., SQLAlchemy's func.*, Django's Func()) can trigger this if they pass Python None as a SQL NULL without an explicit type. Cast at the ORM level using cast(None, Integer) or equivalent.

Frequently Asked Questions

Why does the same call succeed for one argument value but fail for another? Typed values (e.g., a Python integer bound as integer) resolve cleanly because PostgreSQL can apply type preference rules. But when an ORM or driver sends a value as an untyped literal or SQL NULL, there is no concrete type to reason about, so the tie-breaking rules fail. The fix is always to ensure the argument has an explicit type before it reaches the planner.

I only have one function with that name — why is PostgreSQL saying the call is ambiguous? Check whether an extension (e.g., PostGIS, pg_catalog built-ins, or a custom extension) has registered another function with the same name and a compatible signature. Run the pg_proc query above to list every overload PostgreSQL sees. Also verify your search_path — a function in pg_catalog or another schema may be a hidden second candidate.

Does this error abort my transaction? No. SQLSTATE class 42 errors are statement-level errors. The transaction remains open (not in an error state), and you can issue a corrected statement immediately without a ROLLBACK. This differs from runtime errors in class 22 or constraint violations in class 23, which abort the current transaction block.

Can I prevent this error in application code? Yes. Always use parameterized queries with explicitly typed bind parameters rather than interpolating raw literals into SQL. In PostgreSQL client libraries that support type OIDs (e.g., libpq, JDBC), specify the parameter type explicitly. In ORMs, use the library's cast or type-annotation mechanism when calling database functions that have multiple overloads.