Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
ALTER INDEX IF EXISTS
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A popular subclause of DDL statements that jOOQ can usually emulate, is the IF EXISTS clause:
// Renaming the index
create.alterIndexIfExists("old_index").renameTo("new_index").execute();
Dialect support
This example using jOOQ:
alterIndexIfExists("i").renameTo("j")
Translates to the following dialect specific expressions:
Aurora Postgres, CockroachDB, H2, Postgres
ALTER INDEX IF EXISTS i RENAME TO j
DB2
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;
EXECUTE IMMEDIATE '
RENAME INDEX i TO j
';
END
Hana
DO BEGIN
DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 259 BEGIN END;
EXECUTE IMMEDIATE '
RENAME INDEX i TO j
';
END;
MariaDB
BEGIN NOT ATOMIC DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' BEGIN END; DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' BEGIN END; ALTER TABLE RENAME INDEX i TO j; END
MySQL
CREATE PROCEDURE block_1743735353597_5957532() MODIFIES SQL DATA BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' BEGIN END; ALTER TABLE RENAME INDEX i TO j; END; CALL block_1743735353597_5957532(); DROP PROCEDURE block_1743735353597_5957532;
Oracle
BEGIN
EXECUTE IMMEDIATE '
ALTER INDEX i RENAME TO j
';
EXCEPTION
WHEN others THEN
IF sqlerrm LIKE 'ORA-01418%' THEN NULL;
ELSE RAISE;
END IF;
END;
SQLDataWarehouse
BEGIN TRY
EXEC sp_rename 'i', j, 'INDEX'
END TRY
BEGIN CATCH
IF error_number() != 2714 BEGIN
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END;
END CATCH
SQLServer
BEGIN TRY EXEC sp_rename 'i', j, 'INDEX' END TRY BEGIN CATCH IF error_number() != 2714 THROW; END CATCH
ASE, Access, Aurora MySQL, BigQuery, ClickHouse, Databricks, Derby, DuckDB, Exasol, Firebird, HSQLDB, Informix, MemSQL, Redshift, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!