Software Development

Lesser-Known SQL Features: DEFAULT VALUES

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().
 

Lukas Eder

Lukas is a Java and SQL enthusiast developer. He created the Data Geekery GmbH. He is the creator of jOOQ, a comprehensive SQL library for Java, and he is blogging mostly about these three topics: Java, SQL and jOOQ.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button