Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
INSERT .. DEFAULT VALUES
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A lesser-known syntactic feature of SQL is the INSERT .. DEFAULT VALUES statement, where a single record is inserted, containing only DEFAULT values for every row. It is written as such:
INSERT INTO AUTHOR DEFAULT VALUES;
create.insertInto(AUTHOR)
.defaultValues()
.execute();
This can make a lot of sense in situations where you want to "reserve" a row in the database for an subsequent UPDATE statement within the same transaction. Or if you just want to send an event containing trigger-generated default values, such as IDs or timestamps.
The DEFAULT VALUES clause is not supported in all databases, but jOOQ can emulate it using the equivalent statement:
INSERT INTO AUTHOR
(ID, FIRST_NAME, LAST_NAME, ...)
VALUES (
DEFAULT,
DEFAULT,
DEFAULT, ...);
create.insertInto(
AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, ...)
.values(
defaultValue(AUTHOR.ID),
defaultValue(AUTHOR.FIRST_NAME),
defaultValue(AUTHOR.LAST_NAME), ...)
.execute();
The DEFAULT keyword (or DSL#defaultValue() method) can also be used for individual columns only, although that will have the same effect as leaving the column away entirely.
Dialect support
This example using jOOQ:
insertInto(AUTHOR).defaultValues()
Translates to the following dialect specific expressions:
ASE, Access, Aurora MySQL, BigQuery, DB2, Derby, MariaDB, MemSQL, MySQL, Oracle
INSERT INTO AUTHOR VALUES ( DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT )
Aurora Postgres, ClickHouse, CockroachDB, DuckDB, Exasol, Firebird, H2, HSQLDB, Postgres, SQLDataWarehouse, SQLServer, Sybase, Teradata, YugabyteDB
INSERT INTO AUTHOR DEFAULT VALUES
Hana, Informix, Redshift, SQLite, Vertica
INSERT INTO AUTHOR (FIRST_NAME, DATE_OF_BIRTH, YEAR_OF_BIRTH, DISTINGUISHED) VALUES ( NULL, NULL, NULL, NULL )
Snowflake
INSERT INTO AUTHOR (FIRST_NAME, DATE_OF_BIRTH, YEAR_OF_BIRTH, DISTINGUISHED) SELECT NULL, NULL, NULL, NULL
Trino
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!