Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
IF EXISTS
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
For idempotent execution of DDL scripts, the useful IF EXISTS
clause is supported by jOOQ.
// Drop an index create.dropIndexIfExists("index").execute();
Dialect support
This example using jOOQ:
dropIndexIfExists("index")
Translates to the following dialect specific expressions:
-- ACCESS DROP INDEX index -- AURORA_POSTGRES, COCKROACHDB, DUCKDB, H2, HSQLDB, INFORMIX, MARIADB, ORACLE, POSTGRES, SYBASE, YUGABYTEDB DROP INDEX IF EXISTS index -- DB2 BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END; EXECUTE IMMEDIATE ' DROP INDEX index '; END -- FIREBIRD EXECUTE BLOCK AS BEGIN EXECUTE STATEMENT ' DROP INDEX index '; WHEN sqlcode -607 DO BEGIN END END -- HANA DO BEGIN DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 261 BEGIN END; EXECUTE IMMEDIATE ' DROP INDEX index '; END; -- MYSQL CREATE PROCEDURE block_1702641451036_2199457() MODIFIES SQL DATA BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' BEGIN END; DROP INDEX index; END; CALL block_1702641451036_2199457(); DROP PROCEDURE block_1702641451036_2199457; -- SQLDATAWAREHOUSE BEGIN TRY DROP INDEX index END TRY BEGIN CATCH IF error_number() != 3701 BEGIN DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(); DECLARE @ErrorSeverity INT = ERROR_SEVERITY(); DECLARE @ErrorState INT = ERROR_STATE(); RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); END; END CATCH -- SQLITE DROP INDEX IF EXISTS "index" -- SQLSERVER BEGIN TRY DROP INDEX index END TRY BEGIN CATCH IF error_number() != 3701 THROW; END CATCH -- ASE, AURORA_MYSQL, BIGQUERY, DERBY, EXASOL, MEMSQL, REDSHIFT, SNOWFLAKE, TERADATA, TRINO, VERTICA /* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!