ClickHouse DB::Exception: Function already exists

The "DB::Exception: Function already exists" error in ClickHouse occurs when you run CREATE FUNCTION using a name that is already taken -- either by a built-in function or by a previously created user-defined function (UDF). ClickHouse does not allow duplicate function names, and the error code FUNCTION_ALREADY_EXISTS is raised to prevent overwriting an existing function silently.

Impact

The CREATE FUNCTION statement fails, and no new function is registered. The existing function with that name remains unchanged. This is a safe failure -- nothing is overwritten or corrupted.

Common Causes

1. Re-running a migration script -- the CREATE FUNCTION statement was already executed successfully in a previous run.
2. Name collision with a built-in function -- you chose a name that matches one of ClickHouse's hundreds of built-in functions.
3. Another user or process already created the function -- in multi-user environments, someone else may have registered the same UDF name.
4. Cluster-wide function propagation -- on replicated setups, the function may already exist via ON CLUSTER propagation.

Troubleshooting and Resolution Steps

1. Check if the function already exists:

   SELECT name, create_query FROM system.functions
   WHERE origin = 'SQLUserDefined' AND name = 'your_function_name';
   

2. Use CREATE OR REPLACE. If you want to update an existing UDF without error:

   CREATE OR REPLACE FUNCTION my_udf AS (x) -> x * 2;
   

3. Use IF NOT EXISTS. If you only want to create the function when it does not already exist:

   CREATE FUNCTION IF NOT EXISTS my_udf AS (x) -> x * 2;
   

4. Drop the old function first if you need to replace it:

   DROP FUNCTION IF EXISTS my_udf;
   CREATE FUNCTION my_udf AS (x) -> x * 2;
   

5. Check for name collisions with built-in functions:

   SELECT name FROM system.functions WHERE name = 'your_function_name' AND origin != 'SQLUserDefined';
   

   If this returns a result, you are colliding with a built-in function and must choose a different name.

6. Choose a unique naming convention. Prefix UDFs to avoid collisions:

   CREATE FUNCTION udf_my_custom_transform AS (x) -> x * 2;
   

Best Practices

• Use a consistent naming prefix for all UDFs (e.g., udf_, fn_, or your company abbreviation) to avoid collisions with built-in functions.
• Always use CREATE FUNCTION IF NOT EXISTS or CREATE OR REPLACE FUNCTION in migration scripts to make them idempotent.
• Keep a registry of UDF names used across your organization to avoid naming conflicts between teams.
• On clusters, use ON CLUSTER when creating functions to ensure they are registered on all nodes simultaneously.

Frequently Asked Questions

Q: Can I overwrite a built-in ClickHouse function with a UDF?
A: No. ClickHouse does not allow UDFs to shadow built-in functions. If the name is taken by a built-in, you must choose a different name for your UDF.

Q: How do I list all user-defined functions?
A: Query the system.functions table and filter by origin:

SELECT name, create_query FROM system.functions WHERE origin = 'SQLUserDefined';

Q: Does CREATE OR REPLACE FUNCTION work on clusters?
A: Yes, you can use CREATE OR REPLACE FUNCTION ... ON CLUSTER cluster_name to atomically replace a UDF across all nodes.

Q: What happens to queries using a UDF if I drop and recreate it?
A: Running queries that reference the UDF will not be affected mid-execution. However, there is a brief window between DROP and CREATE where new queries referencing the function will fail. Using CREATE OR REPLACE avoids this gap.