A lesser-known SQL feature is the DEFAULT keyword, which can be used in INSERT and UPDATE statements. Consider the following table, created using standard SQL syntax:
CREATE TABLE timestamps (
id INTEGER GENERATED BY DEFAULT
AS IDENTITY(START WITH 1),
t TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT pk_values PRIMARY KEY (id)
)Now, in order to generate a new record in this table, you could either explicitly set a timestamp as such:
INSERT INTO timestamps (t) VALUES (CURRENT_TIMESTAMP);
Or, you just use the handy DEFAULT VALUES syntax:
-- Short syntax INSERT INTO timestamps DEFAULT VALUES; -- Explicit syntax INSERT INTO timestamps (t) VALUES (DEFAULT); INSERT INTO timestamps (id, t) VALUES (DEFAULT, DEFAULT);
The same can be done in an UPDATE statement:
-- Set all timestamps to CURRENT_TIMESTAMP UPDATE timestamps SET t = DEFAULT;
SQL Compatibility for DEFAULT VALUES
As always with SQL, things aren’t as bright as the SQL-92 standard specifies. According to the standard, all of the above must be supported. In reality, this can be said:
Standards-compliant databases
These databases support the standard, fully
- CUBRID
- Firebird
- H2
- HSQLDB (although, there is a bug, which we’ve reported)
- Ingres
- PostgreSQL
- SQLite
- SQL Server
- Sybase SQL Anywhere
Almost compliant databases
These databases support the DEFAULT keyword, but not the DEFAULT VALUES clause for insert statements:
- Access
- DB2
- Derby (we have created DERBY-6444 for this)
- MariaDB
- MySQL
- Oracle
Support for DEFAULT VALUES in jOOQ 3.3
jOOQ 3.3 will support the DEFAULT VALUES syntax and also a very useful variant of it, when combining inserting DEFAULT values with returning them after the insert:
DSL.using(configuration) .insertInto(TIMESTAMPS) .defaultValues() .returning(TIMESTAMPS.ID, TIMESTAMPS.T) .fetch();
The above query will not only insert a new record with default values, but also return those values to your Java program for further processing. As with most jOOQ API elements, the above statement will transparently work with all databases, either through native syntax:
- DB2:
SELECT .. FROM FINAL TABLE (INSERT ..) - PostgreSQL:
INSERT .. RETURNING
… or through JDBC’s Statement.getGeneratedKeys().
