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
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.
Feedback
Do you have any feedback about this page? We'd love to hear it!