PostgreSQL 42725 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42725: Ambiguous Function — Causes and Fixes

PostgreSQL error code 42725 (ambiguous_function) occurs when the database engine cannot determine which overloaded function to call because multiple candidates are equally valid for the provided arguments. This happens when PostgreSQL's function resolution algorithm finds more than one function with the same name that can accept the given arguments through implicit type casting. The fix almost always involves providing explicit type information so PostgreSQL can narrow the candidates down to exactly one.

---

Top 3 Causes

1. Multiple Overloaded Functions with Overlapping Implicit Casts

When two or more functions share the same name but accept different parameter types, passing a value whose type can be implicitly cast to multiple target types triggers the error.

-- Setup: two overloaded functions
CREATE FUNCTION calculate_tax(p_amount integer) RETURNS numeric AS $$
    SELECT p_amount * 0.1;
$$ LANGUAGE sql;

CREATE FUNCTION calculate_tax(p_amount bigint) RETURNS numeric AS $$
    SELECT p_amount * 0.1;
$$ LANGUAGE sql;

-- This raises ERROR 42725 — numeric can cast to both integer and bigint
SELECT calculate_tax(1500.50);

-- Fix: cast explicitly to target type
SELECT calculate_tax(1500.50::integer);
SELECT calculate_tax(1500.50::bigint);

2. Same Function Name Across Multiple Schemas in search_path

When search_path includes multiple schemas and the same function name exists in more than one of them with similar signatures, calling it without a schema qualifier causes ambiguity.

-- Setup: same function in two schemas
CREATE FUNCTION public.format_phone(p_number text) RETURNS text AS $$
    SELECT regexp_replace(p_number, '(\d{3})(\d{4})(\d{4})', '\1-\2-\3');
$$ LANGUAGE sql;

CREATE FUNCTION app.format_phone(p_number text) RETURNS text AS $$
    SELECT '+82-' || regexp_replace(p_number, '(\d{3})(\d{4})(\d{4})', '\1-\2-\3');
$$ LANGUAGE sql;

-- With search_path = 'app, public', this may raise 42725
SELECT format_phone('01012345678');

-- Fix: use schema-qualified function name
SELECT public.format_phone('01012345678');
SELECT app.format_phone('01012345678');

-- Check current search_path
SHOW search_path;

3. String Literals Passed as unknown Type

When you pass a plain string literal as a function argument, PostgreSQL treats it as type unknown, which can implicitly convert to many types (text, varchar, char, etc.). If multiple overloads accept those types, the resolver cannot pick one.

-- Setup: overloaded functions accepting text and varchar
CREATE FUNCTION process_code(p_code text) RETURNS void AS $$
    BEGIN RAISE NOTICE 'text: %', p_code; END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION process_code(p_code varchar) RETURNS void AS $$
    BEGIN RAISE NOTICE 'varchar: %', p_code; END;
$$ LANGUAGE plpgsql;

-- Raises 42725 — 'A001' is type unknown, ambiguous between text and varchar
SELECT process_code('A001');

-- Fix: cast the literal explicitly
SELECT process_code('A001'::text);
SELECT process_code(text 'A001');  -- standard SQL type syntax

-- Inspect all overloads of a function
SELECT
    n.nspname AS schema,
    p.proname AS name,
    pg_get_function_arguments(p.oid) AS args
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = 'process_code';

---

Quick Fix Checklist

1. Always cast arguments explicitly when calling overloaded functions — use ::type or CAST(value AS type).
2. Qualify function names with their schema — public.my_func() instead of my_func().
3. Audit overloaded functions regularly using the pg_proc catalog to find and eliminate unnecessary duplicates.
4. Prefer default parameters over overloading — a single function with DEFAULT values is cleaner and avoids resolution ambiguity entirely.

-- Preferred pattern: single function with defaults instead of overloading
CREATE OR REPLACE FUNCTION calculate_tax(
    p_amount   numeric,
    p_rate     numeric DEFAULT 0.1
)
RETURNS numeric
LANGUAGE sql AS $$
    SELECT p_amount * p_rate;
$$;

-- All of these work without ambiguity
SELECT calculate_tax(10000);
SELECT calculate_tax(10000, 0.15);
SELECT calculate_tax(p_amount := 10000, p_rate := 0.08);

---

Prevention Tips

• Establish a team convention against implicit overloading. When overloading is truly necessary, document each variant clearly and add automated checks in your CI pipeline to detect duplicate function names across schemas.
• Always use typed bind parameters in application code (psycopg2, JDBC, pg-node, etc.) rather than relying on PostgreSQL's implicit type inference. This eliminates the unknown type problem at the source and makes your queries more predictable in production.

---

📖 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.