Skip navigation

Add support for INSERT .. DEFAULT VALUES

score 90
You have not voted. Active

This is a nice little feature that other databases (and the SQL standard) support, which comes in handy especially with tables that have IDENTITY columns (as supported in Oracle 12c), but also with trigger-generated values or other types of DEFAULT values.

 

In PostgreSQL or SQL Server, for instance, I can write:

 

INSERT INTO my_table DEFAULT VALUES

 

In particular, when used with the RETURNING clause, this is rather powerful (PostgreSQL syntax):

 

INSERT INTO my_table DEFAULT VALUES RETURNING *

 

Essentially, this could be seen as syntax sugar for any of these:

 

-- ID is defaulted, and all the other columns are too, implicitly
INSERT INTO my_table (id) VALUES (DEFAULT)


-- If I know the number of columns, I could also do this

INSERT INTO my_table VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT)

 

In any case, the DEFAULT VALUES syntax is a bit more convenient.

Comments

Vote history