Link: This issue replaces CORE1311 [ CORE1311 ]

description: Please add support for the SQL standard DEFAULT context value, which means use the default value of the column assigned (insert, update, merge, etc), or NULL if there is no default. This is defined in (SQL:2011): 6.5 <contextually typed value specification>, with explicit instructions for insert, update, merge, etc. This is already supported by for example PostgreSQL.

This feature should allow for example:

insert into sometable (id, column1) values (default, 'name')

If id is an identity column this will cause the identity value to be generated.

Note that this should also be supported when including other generated/computed columns in the insert (or update, merge, etc) statement:

insert into sometable (column1, column2, somecomputedcolumn) values ('value', 'othervalue', default)

Other examples:

update sometable set column1 = 'a', column2 = default

(similar for merge)

The currently supported "insert into sometable default values" is actually a special case where all columns have default (see 14.11 <insert statement>: "An <insert columns and source> that specifies DEFAULT VALUES is implicitly replaced by an <insert columns and source> that specifies a <contextually typed table value constructor> of the form VALUES (DEFAULT, DEFAULT, ..., DEFAULT) where the number of instances of "DEFAULT" equal to the number of columns of T.")

This ticket should replace CORE1311 which requests this behavior when using NULL, which is not correct behavior.

=>

Please add support for the SQL standard DEFAULT context value, which means use the default value of the column assigned (insert, update, merge, etc), or NULL if there is no default. This is defined in (SQL:2011): 6.5 <contextually typed value specification>, with explicit instructions for insert, update, merge, etc. This is already supported by for example PostgreSQL, SQL Server and Oracle.

This feature should allow for example:

insert into sometable (id, column1) values (default, 'name')

If id is an identity column this will cause the identity value to be generated.

Note that this should also be supported when including other generated/computed columns in the insert (or update, merge, etc) statement:

insert into sometable (column1, column2, somecomputedcolumn) values ('value', 'othervalue', default)

Other examples:

update sometable set column1 = 'a', column2 = default

(similar for merge)

The currently supported "insert into sometable default values" is actually a special case where all columns have default (see 14.11 <insert statement>: "An <insert columns and source> that specifies DEFAULT VALUES is implicitly replaced by an <insert columns and source> that specifies a <contextually typed table value constructor> of the form VALUES (DEFAULT, DEFAULT, ..., DEFAULT) where the number of instances of "DEFAULT" equal to the number of columns of T.")

This ticket should replace CORE1311 which requests this behavior when using NULL, which is not correct behavior.

assignee: Adriano dos Santos Fernandes [ asfernandes ]

Link: This issue is duplicated by CORE665 [ CORE665 ]

summary: Support DEFAULT context value in insert, update, merge, etc => Support DEFAULT context value in INSERT, UPDATE, MERGE and UPDATE OR INSERT statements

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

status: Resolved [ 5 ] => Closed [ 6 ]