The jOOQ User Manual SQL building SQL Statements (DDL) The ALTER statement ALTER TABLE ALTER TABLE .. ADD COLUMN .. FIRST, BEFORE, AFTER

previous : next

ALTER TABLE .. ADD COLUMN .. FIRST, BEFORE, AFTER

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Most RDBMS maintain both a logical and physical ordering of the columns in a table. While the physical ordering (how column values are stored in a disk block) are implementation details that are hidden from the SQL language, the logical ordering is relevant for numerous SQL features, including:

• The behaviour of the SELECT * syntax (the * is expanded according to the logical ordering of columns in a table)
• Client languages, such as PL/SQL, store data in record representations when using TABLE%ROWTYPE and similar syntax
• Libraries like jOOQ will use this ordering for code generation of tables, records, and more
• Client applications and SQL editors like Dbeaver will display results in the declared logical column order

All RDBMS will maintain the logical column ordering declared in the CREATE TABLE statement, and will append new columns to the end of a table when using the ALTER TABLE .. ADD COLUMN statement.

But only few RDBMS allow for modifying the logical column position when altering a table, after the table has been created. This is done with one of the following syntaxes:

// Adding a single column and specify its position
create.alterTable("table").add("column", INTEGER).after("other_column").execute();
create.alterTable("table").add("column", INTEGER).before("other_column").execute();
create.alterTable("table").add("column", INTEGER).first().execute();

AFTER

The AFTER clause will allow for specifying a column after which the new column will be added.

Dialect support

This example using jOOQ:

alterTable("t").add("c", INTEGER).after("other")

Translates to the following dialect specific expressions:

Aurora MySQL, H2, MariaDB, MemSQL, MySQL

ALTER TABLE t ADD c int AFTER other

ClickHouse

ALTER TABLE t ADD COLUMN c Nullable(integer) AFTER other

ASE, Access, Aurora Postgres, BigQuery, CockroachDB, DB2, Derby, DuckDB, Exasol, Firebird, HSQLDB, Hana, Informix, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB

/* UNSUPPORTED */

BEFORE

The BEFORE clause will allow for specifying a column before which the new column will be added.

Dialect support

This example using jOOQ:

alterTable("t").add("c", INTEGER).before("other")

Translates to the following dialect specific expressions:

H2, HSQLDB

ALTER TABLE t ADD c int BEFORE other

ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, Derby, DuckDB, Exasol, Firebird, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB

/* UNSUPPORTED */

FIRST

The FIRST clause will allow for specifying that the new column will be added before all the other columns.

Dialect support

This example using jOOQ:

alterTable("t").add("c", INTEGER).first()

Translates to the following dialect specific expressions:

Aurora MySQL, H2, MariaDB, MemSQL, MySQL

ALTER TABLE t ADD c int FIRST

ClickHouse

ALTER TABLE t ADD COLUMN c Nullable(integer) FIRST

Firebird

EXECUTE BLOCK
AS
BEGIN
  EXECUTE STATEMENT '
    ALTER TABLE t ADD c integer
  ';
  EXECUTE STATEMENT '
    ALTER TABLE t ALTER c POSITION 1
  ';
END

ASE, Access, Aurora Postgres, BigQuery, CockroachDB, DB2, Derby, DuckDB, Exasol, HSQLDB, Hana, Informix, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB

/* UNSUPPORTED */

The jOOQ User Manual SQL building SQL Statements (DDL) The ALTER statement ALTER TABLE ALTER TABLE .. ADD COLUMN .. FIRST, BEFORE, AFTER

previous : next