How to fix Duplicate function in PostgreSQL

Query the pg_proc system catalog to see if your function is already defined:

SELECT proname, pg_get_functiondef(oid)
FROM pg_proc
WHERE proname = 'your_function_name'
AND pg_catalog.pg_function_is_visible(oid);

Replace your_function_name with the name of the function you're trying to create. If results appear, the function exists.

If you want to update an existing function, use CREATE OR REPLACE FUNCTION instead of CREATE FUNCTION:

CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE)
RETURNS INT AS $$
BEGIN
  RETURN DATE_PART('year', AGE(birth_date))::INT;
END;
$$ LANGUAGE plpgsql;

This works as long as you don't change the function signature (name and parameter types). Note: You cannot change parameter names that were previously defined, but you can add new output parameters.

If you need to change the function signature (parameter types, count, or order), you must drop the old function first:

DROP FUNCTION IF EXISTS calculate_age(DATE);

CREATE FUNCTION calculate_age(birth_date DATE)
RETURNS INT AS $$
BEGIN
  RETURN DATE_PART('year', AGE(birth_date))::INT;
END;
$$ LANGUAGE plpgsql;

The IF EXISTS clause prevents errors if the function doesn't exist. Always specify the complete parameter list in the DROP statement.

Update your database migration scripts to check for function existence:

CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE)
RETURNS INT AS $$
BEGIN
  RETURN DATE_PART('year', AGE(birth_date))::INT;
END;
$$ LANGUAGE plpgsql;

Or use conditional drops:

DROP FUNCTION IF EXISTS old_function_name(INT, TEXT) CASCADE;

CREATE FUNCTION new_function_name(id INT, name TEXT)
RETURNS VOID AS $$
BEGIN
  -- function body
END;
$$ LANGUAGE plpgsql;

The CASCADE keyword drops dependent objects (views, triggers, etc.) automatically.

If the function is used in triggers, views, or other objects, dropping it may fail:

DROP FUNCTION IF EXISTS your_function(param_types) CASCADE;

Using CASCADE drops all dependent objects, but verify nothing critical depends on the old function. If it's used in multiple places, update those references to use the new function signature, then drop the old one with CASCADE.