PostgreSQL Duplicate Function (SQLSTATE 42723)

When you attempt to create a function whose name and argument type signature already exist in the current schema, PostgreSQL raises:

ERROR:  function "my_func" already exists with same argument types
SQLSTATE: 42723

The SQLSTATE class is 42 (Syntax Error or Access Rule Violation) and the condition name is duplicate_function.

What This Error Means

PostgreSQL identifies functions not just by name but by their full signature — the combination of schema, function name, and the ordered list of argument data types. This is what enables function overloading: you can have multiple functions named calculate_tax as long as each has a distinct argument list.

SQLSTATE 42723 fires specifically from CREATE FUNCTION (and CREATE PROCEDURE in PostgreSQL 11+) when the exact same schema + name + argument type list combination already exists in pg_proc. PostgreSQL does not allow silently replacing an existing function unless you explicitly use CREATE OR REPLACE FUNCTION.

This error is a statement-level error. The current statement is rolled back, but if you issued it outside of an explicit transaction block, the connection remains open and usable. Inside a transaction block, the transaction is marked as aborted and must be rolled back before you can execute further statements.

Common Causes

1. Running a migration script more than once. A CREATE FUNCTION statement without OR REPLACE will fail on any subsequent run. This is the most common cause in CI pipelines and deployment scripts.

2. Missing OR REPLACE in the DDL. A developer writes CREATE FUNCTION intending to update an existing function but omits OR REPLACE. The existing function with the same signature blocks the operation.

3. Function already created by a database extension or another migration. Utility functions (e.g., updated_at trigger helpers) are sometimes created in shared setup scripts and then referenced again in a later migration.

4. Schema search path differences. A function that appears to be new was already created in a schema that appears earlier in search_path. The duplicate lives in a different schema than expected but is still found.

5. Parallel or out-of-order migration execution. Two concurrent migration runs race and both try to create the same function before either has committed.

How to Fix duplicate_function

1. Use CREATE OR REPLACE FUNCTION instead of CREATE FUNCTION.

   This is the standard, idiomatic fix. It updates the function body, return type (if compatible), language, and options in place without changing ownership or existing grants:

   CREATE OR REPLACE FUNCTION calculate_tax(amount numeric, rate numeric)
   RETURNS numeric
   LANGUAGE sql
   AS $$
     SELECT amount * rate;
   $$;
   

   Note: OR REPLACE cannot change the return type or add/remove arguments. For those changes, drop and recreate the function.

2. Drop the existing function first when you need to change the signature.

   If the argument list or return type must change, you have to drop the old version explicitly:

   DROP FUNCTION IF EXISTS calculate_tax(numeric, numeric);
   
   CREATE FUNCTION calculate_tax(amount numeric, rate numeric, region text)
   RETURNS numeric
   LANGUAGE sql
   AS $$
     SELECT amount * rate;  -- region logic here
   $$;
   

   Use DROP FUNCTION IF EXISTS to make the script idempotent so it does not fail if the old signature is already gone.

3. Guard creation with a conditional check in PL/pgSQL (for complex migration scenarios).

   DO $$
   BEGIN
     IF NOT EXISTS (
       SELECT 1 FROM pg_proc p
       JOIN pg_namespace n ON n.oid = p.pronamespace
       WHERE n.nspname = 'public'
         AND p.proname = 'calculate_tax'
         AND pg_get_function_arguments(p.oid) = 'amount numeric, rate numeric'
     ) THEN
       CREATE FUNCTION public.calculate_tax(amount numeric, rate numeric)
       RETURNS numeric LANGUAGE sql AS $f$ SELECT amount * rate; $f$;
     END IF;
   END
   $$;
   

   This pattern is verbose; prefer CREATE OR REPLACE when you can.

4. Audit existing functions in the target schema before deploying.

   SELECT n.nspname AS schema,
          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 n.nspname NOT IN ('pg_catalog', 'information_schema')
   ORDER BY schema, function_name;
   

Additional Information

• SQLSTATE class 42 covers syntax and access-rule violations. Related codes in the same class include 42710 (duplicate_object), 42P07 (duplicate_table), 42701 (duplicate_column), and 42712 (duplicate_alias).
• CREATE OR REPLACE FUNCTION has been available since PostgreSQL 7.3. There is no version where 42723 was introduced as new behavior — function overloading and the duplicate check have existed since early PostgreSQL.
• PostgreSQL 11 introduced CREATE PROCEDURE. The same duplicate_function error and SQLSTATE apply when a procedure with a matching signature already exists.
• ORM migration frameworks (Alembic, Flyway, Liquibase) do not always wrap function DDL in OR REPLACE automatically. If you are managing functions through raw SQL migration files, audit each CREATE FUNCTION statement.
• psycopg2 and psycopg3 surface this as psycopg2.errors.DuplicateFunction (mapped from SQLSTATE 42723). asyncpg raises asyncpg.DuplicateFunctionError.
• DROP FUNCTION requires specifying the argument types when multiple overloads exist for the same function name, since PostgreSQL must identify exactly which overload to drop.

Frequently Asked Questions

Why does CREATE OR REPLACE FUNCTION sometimes still fail with a different error?

OR REPLACE cannot change a function's return type or remove/add parameters to an existing signature. If you attempt to replace a function and alter its return type, PostgreSQL raises ERROR: cannot change return type of existing function. You must DROP the old function first and recreate it.

Does this error abort my entire transaction?

Yes, if the CREATE FUNCTION statement is inside an explicit transaction block, the transaction is marked aborted (ERROR: current transaction is aborted, commands ignored until end of transaction block). You must issue ROLLBACK before running any further statements. Outside a transaction block, only the CREATE FUNCTION statement itself fails.

Can two functions with the same name but different argument types coexist?

Yes. PostgreSQL fully supports function overloading based on argument types. CREATE FUNCTION add(a integer, b integer) and CREATE FUNCTION add(a numeric, b numeric) can both exist in the same schema simultaneously. The 42723 error only fires when the exact combination of name and argument type list duplicates an existing entry.

How do I find which schema contains the conflicting function?

SELECT n.nspname AS schema,
       pg_get_function_arguments(p.oid) AS arguments
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = 'your_function_name';

Replace 'your_function_name' with the function name from the error message. If you see it in an unexpected schema, check your search_path setting (SHOW search_path;).