PostgreSQL 42723 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42723: Duplicate Function

PostgreSQL error code 42723 (duplicate_function) occurs when you attempt to create a function with a name and argument type signature that already exists within the same schema. Unlike some databases, PostgreSQL supports function overloading — but only when the argument types differ. If the signature is identical, PostgreSQL raises this error immediately.

---

Top 3 Causes

1. Missing OR REPLACE When Redefining a Function

The most common cause. Developers often update function logic and re-run CREATE FUNCTION without the OR REPLACE clause, triggering 42723.

-- ❌ Causes error 42723 if function already exists
CREATE FUNCTION calculate_bonus(p_salary NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN p_salary * 0.10;
END;
$$ LANGUAGE plpgsql;

-- ✅ Correct approach
CREATE OR REPLACE FUNCTION calculate_bonus(p_salary NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN p_salary * 0.10;
END;
$$ LANGUAGE plpgsql;

2. Duplicate Migration Script Execution

When migration tools (Flyway, Liquibase, custom scripts) run the same SQL file more than once due to version control issues or manual DB interventions, the function creation statement is executed again — causing 42723.

-- Check if the function already exists before creating
SELECT p.proname, pg_get_function_identity_arguments(p.oid) AS args
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public'
  AND p.proname = 'calculate_bonus';

-- Safe idempotent pattern
DROP FUNCTION IF EXISTS public.calculate_bonus(NUMERIC);

CREATE FUNCTION public.calculate_bonus(p_salary NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN p_salary * 0.10;
END;
$$ LANGUAGE plpgsql;

3. Type Alias Conflicts

PostgreSQL treats certain type aliases as identical internally. Developers may think they are creating a different overload, but PostgreSQL resolves them to the same type signature.

-- ❌ Both signatures are identical in PostgreSQL
-- 'varchar' and 'character varying' are the same type
CREATE FUNCTION greet(p_name VARCHAR) RETURNS TEXT AS $$
BEGIN RETURN 'Hello, ' || p_name; END;
$$ LANGUAGE plpgsql;

-- This will raise 42723!
CREATE FUNCTION greet(p_name CHARACTER VARYING) RETURNS TEXT AS $$
BEGIN RETURN 'Hi, ' || p_name; END;
$$ LANGUAGE plpgsql;

-- ✅ Verify resolved types before creating overloads
SELECT typname, typtype
FROM pg_type
WHERE typname IN ('varchar', 'text', 'bpchar');

---

Quick Fix Solutions

Fix 1 — Use CREATE OR REPLACE (when return type stays the same)

CREATE OR REPLACE FUNCTION calculate_bonus(p_salary NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN p_salary * 0.15; -- updated logic
END;
$$ LANGUAGE plpgsql;

Fix 2 — Drop and Recreate (when return type must change)

-- Must specify argument types to identify the correct overload
DROP FUNCTION IF EXISTS public.calculate_bonus(NUMERIC);

CREATE FUNCTION public.calculate_bonus(p_salary NUMERIC)
RETURNS TEXT AS $$  -- return type changed to TEXT
BEGIN
    RETURN 'Bonus: $' || (p_salary * 0.15)::TEXT;
END;
$$ LANGUAGE plpgsql;

---

Prevention Tips

1. Standardize on CREATE OR REPLACE across your team.
Make CREATE OR REPLACE FUNCTION the default in all scripts and code reviews. Add a linting rule to flag bare CREATE FUNCTION statements in migration files.

2. Write idempotent migration scripts.
Always pair function creation scripts with DROP FUNCTION IF EXISTS to ensure scripts can be safely re-run without errors. This is especially critical in automated CI/CD pipelines.

-- Idempotent migration template
DROP FUNCTION IF EXISTS public.calculate_bonus(NUMERIC);
CREATE FUNCTION public.calculate_bonus(p_salary NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN p_salary * 0.15;
END;
$$ LANGUAGE plpgsql STABLE;

---

Related Errors

Code	Name	Description
42710	duplicate_object	Object (index, schema) already exists
42P07	duplicate_table	Table already exists
42701	duplicate_column	Column already exists in table
2BP01	dependent_objects_still_exist	Cannot drop function; dependents exist

---

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