|previous : next|
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.
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();
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.