PostgreSQL 42723 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42723: duplicate function — Causes, Fixes & Prevention

What Is Error 42723?

PostgreSQL error code 42723 (duplicate_function) is raised 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 — meaning two functions can share the same name if their argument types differ — but when the full signature matches an existing function exactly, the engine throws this error. It most commonly appears during CREATE FUNCTION or CREATE PROCEDURE execution, especially in migration scripts and CI/CD pipelines.

---

Top 3 Causes

1. Running the Same CREATE FUNCTION Script Twice

The most frequent cause is executing a function creation script more than once without accounting for idempotency. This happens regularly in automated deployment pipelines where scripts are re-run on retry or rollback scenarios.

-- Running this twice will cause error 42723 on the second run
CREATE FUNCTION get_user_age(user_id INT)
RETURNS INT AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(birthdate))
    FROM users WHERE id = user_id;
END;
$$ LANGUAGE plpgsql;

-- ERROR: function get_user_age(integer) already exists

2. Missing OR REPLACE When Updating a Function

Developers intending to modify an existing function often forget to include OR REPLACE, leading to a direct conflict with the existing signature.

-- Wrong: no OR REPLACE
CREATE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN amount * 0.1;
END;
$$ LANGUAGE plpgsql;

-- Correct: always use OR REPLACE for updates
CREATE OR REPLACE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN amount * 0.15; -- updated tax rate
END;
$$ LANGUAGE plpgsql;

3. Schema or search_path Confusion

When search_path is misconfigured or a function already exists in a schema that gets resolved before the intended target, you'll encounter unexpected signature collisions — particularly in multi-tenant or multi-schema architectures.

-- Check where the conflicting function lives
SELECT n.nspname AS schema,
       p.proname AS function,
       pg_get_function_identity_arguments(p.oid) AS args
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.proname = 'calculate_tax';

-- Always qualify your schema explicitly
CREATE OR REPLACE FUNCTION billing.calculate_tax(amount NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN amount * 0.15;
END;
$$ LANGUAGE plpgsql
SET search_path = billing, public;

---

Quick Fix Solutions

Option A — Use CREATE OR REPLACE FUNCTION (Recommended)

CREATE OR REPLACE FUNCTION get_user_age(user_id INT)
RETURNS INT AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(birthdate))
    FROM users WHERE id = user_id;
END;
$$ LANGUAGE plpgsql;

Note: OR REPLACE cannot change a function's return type. If you need to change the return type, use Option B.

Option B — DROP IF EXISTS Then Recreate

Use this when you need to change the return type or clean up before a fresh deployment.

-- Safe idempotent pattern
DROP FUNCTION IF EXISTS billing.calculate_tax(NUMERIC);

CREATE FUNCTION billing.calculate_tax(amount NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN amount * 0.15;
END;
$$ LANGUAGE plpgsql;

---

Prevention Tips

Standardize on CREATE OR REPLACE — Enforce this as a team coding convention and add it to your SQL linter rules (e.g., sqlfluff). Every function DDL in your codebase should use OR REPLACE by default to ensure scripts are safe to re-run.

Audit before deploying — Add a pre-deployment validation step that queries pg_proc to detect signature conflicts before any migration runs:

-- Pre-deployment function audit
SELECT n.nspname AS schema,
       p.proname AS name,
       pg_get_function_identity_arguments(p.oid) AS signature,
       pg_get_function_result(p.oid) AS return_type
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schema, name;

Use migration tools correctly — Tools like Flyway or Liquibase track which scripts have already run. Use versioned, append-only migration files and never edit an already-applied script to avoid duplicate execution issues.

---

Related Errors

• 42710 (duplicate_object): Same concept but for non-function objects like tables or indexes.
• 0A000 (feature_not_supported): Raised when OR REPLACE is used to change a function's return type — you must DROP and recreate instead.
• 2BP01 (dependent_objects_exist): Occurs when dropping a function that other objects depend on; use CASCADE carefully.

---

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