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:

https://spark.apache.org/docs/3.1.2/sql-ref-syntax-dml-insert-into.html

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

Answer 1

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');