Why is Databricks insert using specified columns failing?

Question

I'm trying to do an insert into a table using only specified columns as described here:

If I run the queries below, I get the error shown below. What do I need to do to get this to work?

Code:

create table foo as (
select 
  'foo' as col1,
  'bar' as col2
);

insert into foo (col1) values('col1_val');

Error Message:

Error in SQL statement: DeltaAnalysisException: Column col2 is not specified in INSERT

If your question has been answered, please make sure to accept an answer for further references, @John. — Grimlock, Apr 30 '23 at 08:06

score 1 · Accepted Answer · answered Apr 25 '23 at 15:45

You also need to provide value for col2 while running INSERT INTO query.

Like:

INSERT INTO foo (col1, col2) VALUES ('col1_val', 'col2_val');

Or else you can define default value for col2; then you'd be able to run your existing query:

CREATE TABLE foo (
  col1 STRING,
  col2 STRING DEFAULT 'default_value'
);

INSERT INTO foo (col1) VALUES ('col1_val');

1 Answers1