umzzil nng

Posted on Sep 15 Originally published at oraerror.com

PostgreSQL 42725 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42725: Ambiguous Function — Causes, Fixes & Prevention

What Is Error 42725?

PostgreSQL error code 42725 — ambiguous_function — occurs when a function call matches more than one candidate function in the database, making it impossible for PostgreSQL to determine which one to execute. This typically happens in environments with heavy function overloading or complex implicit type casting chains. Unlike 42883 (undefined function), here PostgreSQL finds too many matching candidates rather than none.

Top 3 Causes

1. Overloaded Functions with Compatible Argument Types

When multiple functions share the same name but differ only in argument types that can be implicitly converted to one another, PostgreSQL cannot pick a unique winner.

-- Two overloaded versions
CREATE FUNCTION calculate(p_val integer) RETURNS integer AS $$
    SELECT p_val * 2;
$$ LANGUAGE SQL;

CREATE FUNCTION calculate(p_val numeric) RETURNS numeric AS $$
    SELECT p_val * 2.5;
$$ LANGUAGE SQL;

-- ERROR 42725: both are equally valid candidates for literal '2'
SELECT calculate(2);

Fix: Use explicit casting to specify the exact type.

-- Resolve by casting explicitly
SELECT calculate(2::integer);
SELECT calculate(2::numeric);

2. User-Defined Function Conflicts with a Built-in Function

Creating a user-defined function with the same name as a PostgreSQL built-in (e.g., round, length, substr) can cause a collision when the argument types are implicitly compatible.

-- User-defined function shadowing a built-in
CREATE FUNCTION round(p_val double precision) RETURNS double precision AS $$
    SELECT floor(p_val + 0.6);
$$ LANGUAGE SQL;

-- ERROR 42725: both pg_catalog.round and public.round are candidates
SELECT round(3.7::double precision);

Fix: Use schema-qualified calls or remove the conflicting user function.

-- Call the built-in explicitly
SELECT pg_catalog.round(3.7::double precision);

-- Call the user-defined version explicitly
SELECT public.round(3.7::double precision);

-- Best option: drop the conflicting user function if not needed
DROP FUNCTION public.round(double precision);

3. Same Function Exists in Multiple Schemas on the search_path

In multi-schema environments, if the same function name (with compatible signatures) exists in two schemas both listed in search_path, PostgreSQL cannot decide which schema takes priority.

-- Both schemas on search_path have calculate(integer)
SET search_path = public, tenant_a;

SELECT calculate(100);
-- ERROR 42725: function calculate(integer) is not unique

Fix: Qualify the call with the target schema or adjust search_path.

-- Schema-qualified call removes ambiguity
SELECT public.calculate(100);
SELECT tenant_a.calculate(100);

-- Or lock search_path at the role level
ALTER ROLE app_user SET search_path = tenant_a, public;

Diagnosing: Find All Candidate Functions

Use this query to list all functions with a given name across schemas:

SELECT
    n.nspname        AS schema_name,
    p.proname        AS function_name,
    pg_get_function_arguments(p.oid)  AS arguments,
    pg_get_function_result(p.oid)     AS return_type
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = 'calculate'   -- replace with your function name
ORDER BY schema_name, function_name;

Prevention Tips

1. Avoid naming user functions after built-ins, and separate overloaded functions clearly.
Establish a team naming convention that keeps function names distinct. If overloading is necessary, ensure argument types are not implicitly convertible to each other. When in doubt, use distinct function names instead.

-- Prefer distinct names over risky overloads
CREATE FUNCTION process_integer(val integer) RETURNS text ...;
CREATE FUNCTION process_numeric(val numeric)  RETURNS text ...;

2. Pin search_path at the role or function level, never rely on session defaults.
Controlling search_path at a fine-grained level eliminates schema-based ambiguity before it reaches production.

-- Lock search_path inside a function definition
CREATE OR REPLACE FUNCTION my_logic()
RETURNS void LANGUAGE plpgsql
SET search_path = app_schema, public
AS $$
BEGIN
    PERFORM calculate(100); -- always resolves to app_schema
END;
$$;

Related Errors

42883 undefined_function — The opposite problem: zero matching candidates found instead of too many.
42846 cannot_coerce — Raised when an explicit cast between incompatible types is attempted during resolution.
42804 datatype_mismatch — Often surfaces alongside overloading fixes when return types disagree.

Explicit casting, schema qualification, and disciplined function naming will resolve and prevent 42725 in virtually every real-world scenario.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community