DROP IF EXISTS & CREATE IF NOT EXISTS in Oracle, MySQL, MariaDB, PostgreSQL, YugabyteDB

To allow idempotent migration scripts, many SQL databases have added an IF EXISTS option to CREATE and an IF NOT EXISTS one to DROP. The syntax and behavior of those popular databases were designed for the best developer experience. Oracle implemented the same syntax but with a little difference in the feedback message that still act as if the statement was run.

The following statements:

drop table if exists demo;
create table if not exists demo ( a int );

run the same on the most popular Open Source databases: SQLite, MySQL, MariaDB, PostgreSQL and compatible like YugabyteDB.

They work as follows:

• if the object already exists, the CREATE statement is skipped
• if the object does not exist, the DROP statement is skipped

When a statement is skipped, it is successful, so that the script can continue. That's what we want for automated scripts that may be run multiple times.

However, to troubleshoot, or simply when running DDL interactively, those databases raise a warning or notice to let the user know that the statement is skipped.

YugabyteDB and PostgreSQL

The PostgreSQL feedback message is the command that was run (CREATE TABLE, DROP TABLE) and an additional NOTICE or WARNING can be displayed :

yugabyte=# create table if not exists demo ( a int );
CREATE TABLE
yugabyte=# create table if not exists demo ( b int );
NOTICE:  relation "demo" already exists, skipping
CREATE TABLE
yugabyte=# drop table if exists demo;
DROP TABLE
yugabyte=# drop table if exists demo;
NOTICE:  table "demo" does not exist, skipping
DROP TABLE

PostgreSQL has many levels of messaging to the logs or the client: log, notice, warning, error and NOTICE is the default.

If you don't want to display this NOTICE, you can set client_min_messages=warning.

MySQL and MariaDB

MySQL mentions a warning when the DDL is skipped. The detail message can be displayed with show warnings:

mysql> create table if not exists demo ( a int );
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table if not exists demo ( b int );
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------+
| Level | Code | Message                     |
+-------+------+-----------------------------+
| Note  | 1050 | Table 'demo' already exists |
+-------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> drop table if exists demo;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists demo;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------+
| Level | Code | Message                 |
+-------+------+-------------------------+
| Note  | 1051 | Unknown table 'db.demo' |
+-------+------+-------------------------+
1 row in set (0.00 sec)

Oracle 23c

Oracle Database has introduced this syntax in 23c to provide a similar user experience. However, be careful, there's no notice or warning when the DDL is skipped:

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> create table if not exists demo ( a int );

Table created.

SQL> create table if not exists demo ( b int );

Table created.

SQL> drop table if exists demo;

Table dropped.

SQL> drop table if exists demo;

Table dropped.

The message depends on the client. This was from sqlplus. Here is the same from SQLcl:

SQL> create table if not exists demo ( a int );

Table DEMO created.

SQL> create table if not exists demo ( b int );

Table DEMO created.

SQL> drop table if exists demo;

Table DEMO dropped.

SQL> drop table if exists demo;

Table DEMO dropped.

In both cases, created may let you think that it was created but it was not. At least not by this statement.

DROP IF NOT EXISTS, CREATE OR REPLACE

Note that the CREATE IF NOT EXISTS differs from CREATE OR REPLACE:

SQL> drop function if exists demo;

Function DEMO dropped.

SQL> create or replace function demo(a int) return varchar2 as begin return '42'; end;
  2* /

Function DEMO compiled

SQL> desc demo

FUNCTION demo RETURNS VARCHAR2

Argument Name    Type          In/Out    Default?
________________ _____________ _________ ___________
A                NUMBER(38)    IN
SQL> create or replace function demo(a varchar) return integer as begin return 42; end;
  2* /

Function DEMO compiled

SQL> desc demo

FUNCTION demo RETURNS NUMBER

Argument Name    Type        In/Out    Default?
________________ ___________ _________ ___________
A                VARCHAR2    IN

Here, the object was replaced and then the structure is the one described in the CREATE statement. In Oracle, it can even change the signature, as I did above by changing the input arguments.

PostgreSQL is more strict about this:

postgres=# create or replace function demo(a int)  returns int as $PL$ begin return '42'; end; $PL$ language plpgsql;
CREATE FUNCTION
postgres=# \df demo
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
 public | demo | integer          | a integer           | func
(1 row)

postgres=# create or replace function demo(a text) returns int as $PL$ begin return  42 ; end; $PL$ language plpgsql;
CREATE FUNCTION
postgres=# \df demo
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
 public | demo | integer          | a integer           | func
 public | demo | integer          | a text              | func
(2 rows)

postgres=# drop function if exists demo(int);
DROP FUNCTION
postgres=# drop function if exists demo(text);
DROP FUNCTION

Summary

All databases are different and even when they provide the same syntax, they may show a different behavior. Today, we see more and more applications developed for PostgreSQL because it is Open Source, community driven, close to the SQL standard, and gives the freedom of migrating within the large world of managed PostgreSQL services. For example on AWS, an application built for PostgreSQL can run with the community release on EC2, or in the managed RDS PostgreSQL, or with distributed storage on AWS Aurora, and even as horizontally scalable Distributed SQL with YugabyteDB. The client messages must be the same for successful commands and notices are the same in all PostgreSQL-compatible databases.

Oracle is used in many legacy applications and users who are using Oracle for a long time will appreciate that the messages did not change so that they can keep their old grep scripts without modifications. There's a discussion about this on the forum. It was designed so that the output is always the same, whether the object exists before or not, and can be compared by regression test suites without false positives. I think an additional message (in the same way as when we create a procedure with compilation errors) would help new users but the client should have an option to ignore it or not, like PostgreSQL client_min_messages, which is not the case. In the past, when working with Oracle, I simply enclosed the DROP and CREATE statements between set termout off and set termout off when their error had to be ignored. The advantage is that it suppresses the display when spooled into a log file, like with automated scripts, but still showing it in interactive mode where the message can help to catch a typo.