Columns with DEFAULT missing error during INSERT

Gim — Tue, 04 Jul 2023 10:16:24 GMT

I am really confused about the DEFAULT capability of Databricks SQL. I looked at the documentation for the minimum required DBR to get the capability yet we still need to enable it as a table property? I updated my cluster's DBR from 12.2 to 13.1.

Anyway, that is already done. After having created my table, I am already set to do my INSERTs. Since I have set default values on 3 (out of 5) of my columns, I only provide the values for the two remaining columns. Yet for some reason, I am still forced to explicitly assign values to the columns with DEFAULTs assigned? What am I doing wrong?

CREATE OR REPLACE TABLE my_table ( id BIGINT GENERATED BY DEFAULT AS IDENTITY, first_name STRING, last_name STRING, _is_active STRING DEFAULT 'Y', _start_date DATE DEFAULT CURRENT_DATE(), _end_date DATE DEFAULT NULL ) TBLPROPERTIES('delta.feature.allowColumnDefaults' = 'enabled', 'delta.minReaderVersion' = '2', 'delta.minWriterVersion'='7') ; -- Initial data INSERT INTO my_table (first_name, last_name) SELECT fname, lname FROM src_table;

I also referred to this link.

Re: Columns with DEFAULT missing error during INSERT

BriceBuso — Tue, 04 Jul 2023 11:40:18 GMT

Hello @Gim,

Got the same problem.

Tried with the instruction "GENERATED ALWAYS AS (CAST(CURRENT_DATE() AS DATE))" but code is returning

"Error in SQL statement: DeltaAnalysisException: current_date() cannot be used in a generated column"

If you find a solution other than generating the current date in your ingestion pipeline and inserting it with the data, please tell me 😁.

topic Columns with DEFAULT missing error during INSERT in Data Engineering

Columns with DEFAULT missing error during INSERT

Re: Columns with DEFAULT missing error during INSERT