What does CREATE TABLE IF NOT EXISTS do when a table of that name already exists with different columns?

## What the guard actually tests `CREATE TABLE IF NOT EXISTS accounts (...)` asks one question before doing anything: does an object named `accounts` already exist in the schema this statement resolves to? If the answer is yes, the engine stops there. It does not parse the intent of the column list against the existing table, it does not diff anything, and it does not report a failure — most engines emit an informational notice or warning and return success. That behaviour is exactly what the clause is for. Its purpose is to make re-running a creation script harmless, not to make a schema converge on a target shape. ## The dangerous gap The gap opens the moment the two definitions differ. Suppose an early version of a bootstrap script created: ```sql CREATE TABLE IF NOT EXISTS accounts ( account_id BIGINT NOT NULL, email VARCHAR(255) ); ``` and a later version of the same script says: ```sql CREATE TABLE IF NOT EXISTS accounts ( account_id BIGINT NOT NULL, email VARCHAR(255), created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, is_verified BOOLEAN NOT NULL DEFAULT FALSE ); ``` On a fresh database the second script produces the four-column table. On a database that ran the first script, it produces nothing at all. Both runs report success. The environments have silently diverged, and the divergence is discovered later — often in production — when a query references `is_verified` and the engine reports an unknown column. This is the reason experienced teams treat `IF NOT EXISTS` in a change script as a smell: it converts a loud, immediate, easy-to-diagnose failure into a quiet one that surfaces far from its cause. ## It is a name match, not an identity match Two further subtleties follow from "name check": - **Schema qualification matters.** `CREATE TABLE IF NOT EXISTS accounts (...)` resolves `accounts` against the current schema or search path. If `analytics.accounts` exists but the statement resolves to `public.accounts`, the guard finds nothing and the table is created. Qualify the name when the target matters. - **Any object of that name may block it.** In engines where tables and views share a namespace, an existing view named `accounts` is enough to make the statement skip, which is a particularly confusing outcome. ## Where it is genuinely useful The clause earns its place where a script must be safely re-runnable and the definition is stable: - test and local-development bootstrap that may be re-run any number of times - creating a scratch or staging table inside a job that might be retried - setup scripts shipped with a tool, where the table either exists in the expected shape or does not exist at all In all of these, "already there" genuinely means "already correct". ## Where to use something else For evolving a real schema, use a versioned migration tool. Its model is the opposite of `IF NOT EXISTS`: each change runs exactly once, in order, recorded in a ledger, and an unexpected pre-existing object is an error a human must look at. That loudness is the feature. If a migration must tolerate a table that may or may not exist, the honest form is an explicit check with an explicit branch, so the ambiguity is visible in the script rather than swallowed by a keyword. A related trap is using `IF NOT EXISTS` to make a script idempotent while the corresponding `ALTER TABLE` statements have no such guard — the create silently skips, and then the alters fail against a table that lacks the expected columns. Idempotency has to be a property of the whole script, not of its first statement. ## Portability `IF NOT EXISTS` is not part of the SQL standard; it is a widely adopted vendor extension. PostgreSQL, MySQL and SQLite all support it on `CREATE TABLE`. Some engines do not, and there the equivalent is a query against the information schema or catalog followed by a conditional create, or catching the "object already exists" error. Because the spelling and availability vary, a script that must run everywhere is better served by a migration tool than by the keyword.