Oracle 23c New features - IF EXISTS and IF NOT EXISTS with CREATE, ALTER, and DROP commands Tips
IF EXISTS
Use IF EXISTS and IF NOT EXISTS with CREATE, ALTER, and DROP commands for different object types
To ensure that the DDL statements are idempotent, the CREATE, ALTER, and DROP commands support the IF EXISTS and IF NOT EXISTS clauses.
We can use these clauses to check if a given object exists or does not exist, and ensure that if the check fails, the command is ignored and does not generate an error.
Drop table with if exists
HR@pdb1> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
HR@pdb1> drop table t1;
drop table t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
HR@pdb1> drop table if exists t1;
Table dropped.
HR@pdb1>
Create table with if not exists
HR@pdb1> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
HR@pdb1> create table t1(id number);
Table created.
HR@pdb1> select * from t1;
no rows selected
HR@pdb1> create table if not exists t1(id number);
Table created.
HR@pdb1> create table if not exists t1(id varchar2(10));
Table created.
HR@pdb1>
The following object types are supported for CREATE … IF NOT EXISTS, ALTER … IF EXISTS, and DROP … IF EXISTS DDL statements.
Table 9-8 Object Types Supported for CREATE, ALTER, and DROP Commands
Reference
Oracle Database 23c / SQL Language Reference / Using IF EXISTS and IF NOT EXISTS
PostgresSQL has the same feature. create table
IF NOT EXISTS
Do not throw an error if a relation with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing relation is anything like the one that would have been created.
Refer:
Have a good work&life! 2022/12 via LinHong