[FEATURE REQUEST] DEFAULT values for columns

[azure2439]

Bug

INSERT INTO DELTA TABLE Fails When Specifying Only Part of the Columns in the List

Describe the problem

If there are 10 fields in a Delta Table, if I try to Insert Data into only 5 columns using Insert into syntax, it fails saying the 6th column name is not in the Insert into column List. In SQL Server, by default it inserts NULL value in columns not specified, the same doesn't work in Delta table?

Thanks

Steps to reproduce

Observed results

Expected results

Further details

Environment information

• Delta Lake version:
• Spark version:
• Scala version:

Willingness to contribute

The Delta Lake Community encourages bug fix contributions. Would you or another member of your organization be willing to contribute a fix for this bug to the Delta Lake code base?

• Yes. I can contribute a fix for this bug independently.
• Yes. I would be willing to contribute a fix for this bug with guidance from the Delta Lake community.
• No. I cannot contribute a bug fix at this time.

[zsxwing]

This behavior is expected. Currently Delta doesn't support setting a default value for a column. Spark added it recently: https://issues.apache.org/jira/browse/SPARK-38334 We will look at it and see how to integrate Delta with the default values support in Spark.

[azure2439]

Thank you

[elenigeo]

Hello. I was informed in the latest office hours that default value is available for delta tables as well https://docs.databricks.com/release-notes/runtime/11.3.html. However I still get the following error even after upgrading to 12.2 version.

AnalysisException: Failed to execute CREATE TABLE command because DEFAULT values are not supported for target data source with table provider: "".

I was able to set it up only in the following way:

CREATE or replace TABLE test_default (ID int, Type Varchar(2), Type2 Varchar(4));
ALTER TABLE test_default SET TBLPROPERTIES('delta.minReaderVersion' = '1', 'delta.minWriterVersion' = '7');
ALTER TABLE test_default SET TBLPROPERTIES('delta.feature.allowColumnDefaults' = 'enabled');
ALTER TABLE test_default ALTER COLUMN Type2 SET DEFAULT '-100';
DESCRIBE TABLE EXTENDED test_default;

Do you have any update when this functionality will be available also in the create statement for delta tables?

Thank you

[scottsand-db]

@elenigeo can you reach out to Databricks support?

[jboarman]

@elenigeo Thanks! Those table properties worked for me perfectly in DRB 13.0 (Spark 3.4.0)!

[parquetsharp]

@zsxwing hi, is there plan that the default value will be supported in delta aligned with Spark 3.4? Thanks

[dtenedor]

Please follow: #2238