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
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().
Like this:
Like Loading...
Published by lukaseder
I made jOOQ
View all posts by lukaseder
This is a thing I wish I knew those times I had to joggle with now() and sysdate.
Yeah. Surprisingly, few people know this…
Hey, I’m new to Jooq. Is there a way to specify to send the
DEFAULTkeyword for just one field?Yes. We still need to document this in the manual, but there’s
DSL.defaultValue()for this, which can be used withINSERTorUPDATEstatements.