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

4 thoughts on “Lesser-Known SQL Features: DEFAULT VALUES

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s