topic Re: Omitting columns in an INSERT statement does not seem to work despite meeting the requirements in Data Engineering

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

fuselessmatt — Mon, 03 Apr 2023 14:01:13 GMT

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 
('test')

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

NandiniN — Mon, 03 Apr 2023 15:40:57 GMT

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.

CREATE TABLE Table (

col1 string NOT NULL DEFAULT 'abc'

)

USING Delta

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

Hope this helps.

Thanks & Regards,

Nandini

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

fuselessmatt — Mon, 03 Apr 2023 16:55:44 GMT

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?

https://docs.databricks.com/sql/language-manual/sql-ref-syntax-ddl-create-table-using.html

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.

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

Anonymous — Tue, 04 Apr 2023 05:31:32 GMT

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!