Available in versions: Dev (3.21)
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
ALTER TABLE .. CHANGE COLUMN
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
jOOQ supports the MySQL style CHANGE COLUMN clause, which can be used to both rename and change the type of a column in one go:
// Rename a column
create.alterTable("table").changeColumn("old_column", "new_column").set(INTEGER).execute();
It is generally not recommended to use this legacy MySQL statement, which is supported by jOOQ for interoperability purposes only.
Dialect support
This example using jOOQ:
alterTable("t").changeColumn("c", "d").set(INTEGER)
Translates to the following dialect specific expressions:
Aurora MySQL, MariaDB, MySQL
ALTER TABLE t CHANGE COLUMN c d int
Aurora Postgres, Postgres, YugabyteDB
DO $$ BEGIN ALTER TABLE t RENAME COLUMN c TO d; ALTER TABLE t ALTER d TYPE int; END; $$
DB2
BEGIN EXECUTE IMMEDIATE 'ALTER TABLE t RENAME COLUMN c TO d'; EXECUTE IMMEDIATE 'ALTER TABLE t ALTER d SET DATA TYPE integer'; END
Exasol
BEGIN ALTER TABLE t RENAME COLUMN c TO d; ALTER TABLE t MODIFY d int; END;
Firebird
EXECUTE BLOCK AS BEGIN EXECUTE STATEMENT 'ALTER TABLE t ALTER COLUMN c TO d'; EXECUTE STATEMENT 'ALTER TABLE t ALTER d TYPE integer'; END
H2
CREATE ALIAS block_1749296421439_855715 AS $$
void x(Connection c) throws SQLException {
try (PreparedStatement s = c.prepareStatement(
"ALTER TABLE t ALTER COLUMN c RENAME TO d"
)) {
s.execute();
}
try (PreparedStatement s = c.prepareStatement(
"ALTER TABLE t ALTER d int"
)) {
s.execute();
}
}
$$;
CALL block_1749296421439_855715();
DROP ALIAS block_1749296421439_855715;
Hana
DO BEGIN EXECUTE IMMEDIATE 'RENAME COLUMN c TO d'; EXECUTE IMMEDIATE 'ALTER TABLE t ALTER(d integer)'; END;
HSQLDB
BEGIN ATOMIC ALTER TABLE t ALTER COLUMN c RENAME TO d; ALTER TABLE t ALTER d int; END;
Informix
BEGIN RENAME COLUMN c TO d; ALTER TABLE t MODIFY d integer; END;
Oracle
BEGIN EXECUTE IMMEDIATE 'ALTER TABLE t RENAME COLUMN c TO d'; EXECUTE IMMEDIATE 'ALTER TABLE t MODIFY d number(10)'; END;
Snowflake
BEGIN ALTER TABLE t RENAME COLUMN c TO d; ALTER TABLE t ALTER d number(10); END;
SQLDataWarehouse
BEGIN ALTER TABLE t RENAME COLUMN c TO d; ALTER TABLE t ALTER COLUMN d int; END;
SQLServer
BEGIN EXEC sp_rename 't.c', d, 'COLUMN'; ALTER TABLE t ALTER COLUMN d int; END;
ASE, Access, BigQuery, ClickHouse, CockroachDB, Databricks, Derby, DuckDB, MemSQL, Redshift, SQLite, Sybase, Teradata, Trino, Vertica
/* 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!