Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Data Definition

SQL IF EXISTS and IF NOT EXISTS

Reference for IF EXISTS and IF NOT EXISTS in SQL DDL: write scripts that can run twice, which statements accept them and where dialects differ.

Schema scripts usually run more than once: in test setups, on every deploy, after a failed attempt. A plain CREATE TABLE fails if the table is already there and a plain DROP TABLE fails if it is not. The IF NOT EXISTS and IF EXISTS clauses turn those errors into quiet no-ops.

They check the name only. CREATE TABLE IF NOT EXISTS does not compare columns or update the structure, so an old table with a different shape stays as it is.

SQL
CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT);
DROP TABLE IF EXISTS notes;

Try it

SQL A script that can run twice ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL IF NOT EXISTS ignores a different definition ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
CREATE TABLE IF NOT EXISTS name (...);
CREATE INDEX IF NOT EXISTS name ON table (...);
CREATE VIEW IF NOT EXISTS name AS ...;
DROP TABLE IF EXISTS name;
DROP INDEX IF EXISTS name;
DROP VIEW IF EXISTS name;
Clause Effect
IF NOT EXISTS Skip the creation if an object with that name exists.
IF EXISTS Skip the drop if there is no such object.

Dialect differences

Dialect Notes
SQLite Supported on CREATE and DROP of tables, indexes, views, triggers. ALTER TABLE ADD COLUMN has no such clause.
PostgreSQL Supported broadly, including ADD COLUMN IF NOT EXISTS and DROP COLUMN IF EXISTS; emits a notice when skipping.
MySQL IF NOT EXISTS / IF EXISTS on most objects (a warning is raised, see SHOW WARNINGS); column-level clauses exist only in MariaDB.
SQL Server DROP ... IF EXISTS since 2016; there is no IF NOT EXISTS on CREATE TABLE: test with IF OBJECT_ID('t') IS NULL.
Oracle Both clauses arrived in 23ai; earlier versions catch the error in PL/SQL.
SQL
-- SQL Server
IF OBJECT_ID('dbo.notes', 'U') IS NULL
  CREATE TABLE dbo.notes (id INT PRIMARY KEY, body NVARCHAR(200));

-- PostgreSQL
ALTER TABLE notes ADD COLUMN IF NOT EXISTS created_at timestamptz;

Usage notes

  • Not a migration tool. If the structure differs, nothing is changed; use versioned migrations that alter tables deliberately.
  • Hidden mistakes: a typo in a name with DROP ... IF EXISTS succeeds silently, so you may think something was removed that never was.
  • Race conditions: on a busy server two sessions can both pass the check; the database usually still keeps the second from creating a duplicate.
  • CREATE ... IF NOT EXISTS plus data: the insert after it will run each time, so make it idempotent (see UPSERT or INSERT OR IGNORE).
  • Not to be confused with the EXISTS operator used in queries.