Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
Showing results for 
Search instead for 
Did you mean: 

Omitting columns in an INSERT statement does not seem to work despite meeting the requirements


We want to use the INSERT INTO command with specific columns as specified in the official documentation. The only requirements for this are

️ Databricks SQL warehouse version 2022.35 or higher

️ Databricks Runtime 11.2 and above

and the behaviour should be as follows

If this command omits a column, Databricks SQL assigns the corresponding default value instead.

If the target table schema does not define any default value for the inserted column, Databricks SQL assigns NULL if the column is nullable. Otherwise, Databricks SQL raises an error.

We are using SQL Warehouse 2023.15 (> 2022.35) and I guess our Databricks Runtime is on 12.2. Our interpretation of the documentation is therefore that this should work, but it does not. Why does it not work? Historically it did not support Delta-tables, but according to the documentation, that should not be an issue

create table if not exists prod_gold.forecaster.test
    col_1      string,
    col_2      string
INSERT INTO prod_gold.forecaster.test (col_1) VALUES 


Honored Contributor
Honored Contributor

Hello @Mattias P​ ,

In your statement above, the create table command should contain the defaults,

but when I tried it, it failed saying defaults are not allowed.

Initially this was not supported with Delta, but when this feature was introduced in Delta protocol we had to add it in TBLPROPERTIES. Note - this is only for Delta, other protocol works without any TBLPROPERTIES. The doc will be updated to reflect this.


col1 string NOT NULL DEFAULT 'abc'



TBLPROPERTIES('delta.feature.allowColumnDefaults' = 'supported')

Hope this helps.

Thanks & Regards,


Thanks for helping us with this. Are you sure about that? It seems the documentation says it should still be possible

"If the target table schema does not define any default value for the inserted column, Databricks SQL assigns NULL if the column is nullable"

And everything is nullable unless you explicitly set NOT NULL?

I think it is important to be clear here as different databases systems handles this differently. In PostgreSQL for example it

Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none.

In MySQL it becomes implicit or explicit default values (not NULL?)

If strict SQL mode is not enabled, any column not explicitly given a value is set to its default (explicit or implicit) value. 

Not applicable

Hi @Fusselmanwog​ 

Thank you for posting your question in our community! We are happy to assist you.

To help us provide you with the most accurate information, could you please take a moment to review the responses and select the one that best answers your question?

This will also help other community members who may have similar questions in the future. Thank you for your participation and let us know if you need any further assistance! 

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!