Issue (not bug) with generated columns

Consider this:

.print Issue with generated columns

create table a(n,s as (n+1));
insert into a values(1),(2),(3);
select * from a;

create table b as select * from a;
.print table b converted the generated column into a regular column
select * from b;

delete from a;
insert into a select * from b;
select * from a;

.print Fails as there are two real columns in b but only one in a
-- Error: near line 12: table a has 1 columns but 2 values were supplied


It’s common practice (for some of us, at least) to copy a table to another for manipulation,
and then copy the finished work back to the original table.

With generated columns the new table gets the generated columns as regular columns (no complains).
Now, copying back to the original table produces an error as there is a mismatch in the number of real columns.

Of course one may specify each and every column manually (and there could many of them).

Suggestions:

1. It’d be nice to have some way to specify `select *` that would ignore generated columns.
Maybe, `select real *` or something of that sort to indicate we want `*` to select only actual columns, not virtual.

2. Another possibility would be, when copying over virtual columns, the column to be counted (for position) but data ignored as it’s read-only.

Thank you.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users