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, Teradata, YugabyteDB

INSERT INTO AUTHOR
DEFAULT VALUES

Hana, Informix, Redshift, SQLite, Sybase, 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

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo