How to create delta table with identity column.

[TheOptimizer]

I'm sure this is probably some oversight on my part, but I don't see it. I'm trying to create a delta table with an identity column. I've tried every combination of the syntax I can think of.

%sql
create or replace table IDS.picklist
( picklist_id            BIGINT not null GENERATED ALWAYS AS IDENTITY,
  picklist_Name          string not null comment 'The unique name of the picklist.  Should be indicative of source and specific to the data requested.',
)
using delta
location 'dbfs:/mnt/dl_ids/databricks_database/picklist.delta'
;

[TheOptimizer]

I figured it out but am still somewhat confused. I was originally trying to create the table using sql magic commend in a notebook. It worked when I copied the sql into a databricks sql query.

[Kaniz]

Hi @Thomas Wilson​, I'm glad you figured it out. Would you mind selecting your answer as the Best? It would help the entire community as well. Thank you.

[anu1]

Could you please explain clearly on " It worked when I copied the sql into a databricks sql query." how this is done.

[TheOptimizer]

Sure thing. Originally, I was trying to run my creation in a %sql magic cell of a notebook. The 2nd attemp (which worked) I pasted the DDL in the SQL environment of databricks. Instead of being in Data and Engineering envinronment, select SQL. Hope this helps, best I have.

[lucas_marchand · Accepted solution]

I was also having this same error and my cluster was running Databricks Runtime Version 9.1 so I changed it to 11.0 and it worked.

[TheOptimizer]

Interesting. I would then guess that what I experienced was a bug, and that the upgrade fixed this. I tend to stay on the LTS, so I am currently running 9.1 DBR. Thanks for the good news!