ClickHouse DB::Exception: Table already exists

The "DB::Exception: Table already exists" error is raised when you run a CREATE TABLE statement for a table name that already exists in the target database. ClickHouse flags this with the error code TABLE_ALREADY_EXISTS. It is one of the most frequently encountered DDL errors, especially in automated or repeated migration scripts.

Impact

The CREATE TABLE statement is rejected, but the existing table and its data are not affected. The danger is indirect: if your script expects the table to be freshly created with a specific schema, the existing table may have a different structure, leading to subtle data issues downstream.

Common Causes

1. Running a migration script more than once -- a CREATE TABLE without IF NOT EXISTS will fail on the second run.
2. Concurrent deployments -- two processes attempting to create the same table at roughly the same time.
3. Leftover tables from a failed previous operation -- a partially completed migration left a table behind.
4. Name collision across environments -- copying DDL from one environment to another where the table already exists.
5. Replicated table recreation -- on a ClickHouse cluster, the table may already exist on some replicas after a partial rollout.

Troubleshooting and Resolution Steps

1. Use IF NOT EXISTS to make the statement idempotent:

   CREATE TABLE IF NOT EXISTS my_database.my_table (
       id UInt64,
       name String
   ) ENGINE = MergeTree()
   ORDER BY id;
   

   This is the simplest fix and works well for most migration scenarios.

2. Check whether the existing table matches your expected schema:

   SHOW CREATE TABLE my_database.my_table;
   

   Compare the output with what your script expects. If the schemas match, adding IF NOT EXISTS is safe.

3. If you need to replace the table, drop it first:

   DROP TABLE IF EXISTS my_database.my_table;
   CREATE TABLE my_database.my_table (...) ENGINE = MergeTree() ORDER BY id;
   

   Be aware that this destroys all data in the existing table.

4. For schema changes, use ALTER TABLE instead of re-creating:

   ALTER TABLE my_database.my_table ADD COLUMN new_col String DEFAULT '';
   

5. On a cluster, check all replicas. The table may exist on some nodes but not others. Use ON CLUSTER for distributed DDL:

   CREATE TABLE IF NOT EXISTS my_database.my_table ON CLUSTER my_cluster (...)
   ENGINE = ReplicatedMergeTree(...)
   ORDER BY id;
   

6. Inspect system.tables for details about the existing table:

   SELECT database, name, engine, create_table_query
   FROM system.tables
   WHERE name = 'my_table';
   

Best Practices

• Always use IF NOT EXISTS in migration scripts and automated DDL to make them safely re-runnable.
• Version your database schema and track which migrations have already been applied.
• In clustered environments, use ON CLUSTER to ensure consistent DDL across all nodes.
• Before dropping and recreating a table, verify that you have a backup or that the data loss is acceptable.
• Separate schema creation from data loading so each step can be retried independently.

Frequently Asked Questions

Q: Is IF NOT EXISTS safe to use in production?
A: Yes. When the table already exists, the statement simply does nothing and returns success. It does not modify the existing table or its data.

Q: What if the existing table has a different schema than what my CREATE TABLE specifies?
A: IF NOT EXISTS will silently skip the creation. It does not compare schemas. If you need schema enforcement, check the existing schema with SHOW CREATE TABLE and use ALTER TABLE to reconcile differences.

Q: How do I handle this error in a ClickHouse cluster?
A: Use CREATE TABLE IF NOT EXISTS ... ON CLUSTER cluster_name to distribute the DDL. This handles the case where some replicas already have the table and others do not.

Q: Can two CREATE TABLE statements race and both succeed?
A: No. ClickHouse serializes DDL operations on the same table name within a database. One will succeed and the other will get TABLE_ALREADY_EXISTS (unless both use IF NOT EXISTS).