cancel
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.
cancel
Showing results for 
Search instead for 
Did you mean: 

Issue with inserting multiple rows in Delta table with identity column

bb2312
New Contributor II

Running DBR 11.3 / Azure Databricks

Table definition below:

%sql
CREATE OR REPLACE TABLE demo2 (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY,
  product_type STRING,
  sales BIGINT
)
USING DELTA
LOCATION '/folderlocation/'
TBLPROPERTIES (
'delta.columnMapping.mode' = 'name',
   'delta.minReaderVersion' = '2',
   'delta.minWriterVersion' = '5')

Inserting 1 row works fine

%sql
INSERT INTO demo2 (product_type, sales)
VALUES ("cell", 130000);

On attempting to insert a new row I get this error:

"AnalysisException: Column id is not specified in INSERT"

I can insert columns explicitly as follows:

%sql
INSERT INTO demo2 (id,product_type, sales)
VALUES (3,"batt", 130000);

I can even insert duplicate column values like so

err2I have tried changing the identity definition to "GENERATED ALWAYS AS IDENTITY" however get the same result. Am I doing this incorrectly or missing something?

2 REPLIES 2

-werners-
Esteemed Contributor III

https://github.com/delta-io/delta/issues/1215

It is an open issue.

You can enter your own values because you use BY DEFAULT instead of ALWAYS.

bb2312
New Contributor II

Just updating it is possible this issue has now been addressed.

As before working on Azure Databricks 11.3 DBR

Inserting into managed table:

image.pngAlso appears to be addressed for autoloader insertion into unmanaged table

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group