Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Add support for INSERT .. DEFAULT VALUES

Lukas EderJul 24 2016

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
Post Details
Added on Jul 24 2016
7 comments
10,853 views