About 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.

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

Related Whitepaper:

Software Architecture

This guide will introduce you to the world of Software Architecture!

This 162 page guide will cover topics within the field of software architecture including: software architecture as a solution balancing the concerns of different stakeholders, quality assurance, methods to describe and evaluate architectures, the influence of architecture on reuse, and the life cycle of a system and its architecture. This guide concludes with a comparison between the professions of software architect and software engineer.

Get it Now!  

Leave a Reply


three × = 24



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.

Sign up for our Newsletter

20,709 insiders are already enjoying weekly updates and complimentary whitepapers! Join them now to gain exclusive access to the latest news in the Java world, as well as insights about Android, Scala, Groovy and other related technologies.

As an extra bonus, by joining you will get our brand new e-books, published by Java Code Geeks and their JCG partners for your reading pleasure! Enter your info and stay on top of things,

  • Fresh trends
  • Cases and examples
  • Research and insights
  • Two complimentary e-books