Your Web News in One Place

An Interest In:

Web News this Week

Search Archive

Some of Our Sources

View All Sources

Help Webnuz

Referal links:

Sign up for GreenGeeks web hosting
April 19, 2023 09:29 pm GMT

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 TABLEyugabyte=# create table if not exists demo ( b int );NOTICE:  relation "demo" already exists, skippingCREATE TABLEyugabyte=# drop table if exists demo;DROP TABLEyugabyte=# drop table if exists demo;NOTICE:  table "demo" does not exist, skippingDROP 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-ReleaseVersion 23.2.0.0.0SQL> 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 compiledSQL> desc demoFUNCTION demo RETURNS VARCHAR2Argument Name    Type          In/Out    Default?________________ _____________ _________ ___________A                NUMBER(38)    INSQL> create or replace function demo(a varchar) return integer as begin return 42; end;  2* /Function DEMO compiledSQL> desc demoFUNCTION demo RETURNS NUMBERArgument 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 FUNCTIONpostgres=# \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 FUNCTIONpostgres=# \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 FUNCTIONpostgres=# 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 for successful commands and notices are the same.

Oracle is used in many legacy applications and users who are already using Oracle will appreciate that the messages did not change so that they can keep their old grep scripts. I think an additional warning (in the same way as when we create a procedure with compilation errors) would help new users.


Original Link: https://dev.to/aws-heroes/drop-if-exists-create-if-not-exists-in-oracle-mysql-mariadb-postgresql-yugabytedb-pb1

Share this article:    Share on Facebook
View Full Article

Dev To

An online community for sharing and discovering great ideas, having debates, and making friends

More About this Source Visit Dev To