allow columns to be specified in insert statements

[talagluck]

Description

I would expect this to work:

  insert into my_table ("column1", "column2", "column3")
        select "column1", "column2", "column3"
        from my_other_table
  

But it currently doesn't work if you specify the columns. This isn't necessarily how I would intuitively choose to do it, but it is how the dbt postgres adapter compiles incrementally materialized models, so fixing this would enable that feature.

[vrongmeal]

> create table my_table as values (1, 2, 3, 4, 5);
Table created

> create table my_other_table as values (10, 20, 30, 40);
Table created

> select column1, column2, column3 from my_other_table;
┌─────────┬─────────┬─────────┐
│ column1 │ column2 │ column3 │
│      ── │      ── │      ── │
│   Int64 │   Int64 │   Int64 │
╞═════════╪═════════╪═════════╡
│      10 │      20 │      30 │
└─────────┴─────────┴─────────┘

> select * from my_table;
┌─────────┬─────────┬─────────┬─────────┬─────────┐
│ column1 │ column2 │ column3 │ column4 │ column5 │
│      ── │      ── │      ── │      ── │      ── │
│   Int64 │   Int64 │   Int64 │   Int64 │   Int64 │
╞═════════╪═════════╪═════════╪═════════╪═════════╡
│       1 │       2 │       3 │       4 │       5 │
└─────────┴─────────┴─────────┴─────────┴─────────┘

> insert into my_table ("column1", "column2", "column3")
:::         select "column1", "column2", "column3"
:::         from my_other_table;
Inserted 1 row

> select * from my_table;
┌─────────┬─────────┬─────────┬─────────┬─────────┐
│ column1 │ column2 │ column3 │ column4 │ column5 │
│      ── │      ── │      ── │      ── │      ── │
│   Int64 │   Int64 │   Int64 │   Int64 │   Int64 │
╞═════════╪═════════╪═════════╪═════════╪═════════╡
│      10 │      20 │      30 │    NULL │    NULL │
│       1 │       2 │       3 │       4 │       5 │
└─────────┴─────────┴─────────┴─────────┴─────────┘

This works for me. I suppose Datafusion fixed this. @talagluck where/when did you face this error?

[tychoish]

can you put this in as an SLT?

[vrongmeal]

Sure, will do!

[talagluck]

Yes, I'm having trouble reproducing now, and I'm not sure whether it was fixed in Datafusion, or whether I was just hitting multiple errors at the same time. We can close this once the SLTis added.

[tychoish]

@talagluck do you want to add the SLT?