Omitting columns in an INSERT statement does not seem to work despite meeting the requirements
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-03-2023 07:01 AM
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')
- Labels:
-
Columns
-
Insert
-
Sql Warehouse
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-03-2023 08:40 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-03-2023 09:55 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-03-2023 10:31 PM
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!