SQLite Forum

Metadata missing for generated columns
Login

Metadata missing for generated columns

(1.1) By J-L Hainaut (JLHainaut) on 2020-06-25 20:02:29 edited from 1.0 [link]

I have particularly appreciated the recent addition of "generated column" in SQLite. However, it seems that their description is missing in system table "table_info" (sqlite 3.32). 
Let us consider this small schema:

<code>
create table S(Sid integer not null primary key);
create table T(Tid integer not null primary key,
               C1 integer,
               C2 integer generated always as (2*C1) references S);
create index XC2 on T(C2);
create view VT as select C2 from T;
</code>

Generated column "C2" does not appear in system table table_info('T'):

<code>
select * from pragma_table_info('T');
+-----+------+---------+---------+------------+----+
| cid | name | type    | notnull | dflt_value | pk |
+-----+------+---------+---------+------------+----+
| 0   | Tid  | integer | 1       | --         | 1  |
| 1   | C1   | integer | 0       | --         | 0  |
+-----+------+---------+---------+------------+----+
</code>

However, "C2" in view VT is correctly referenced:

<code>
select * from pragma_table_info('VT')
+-----+------+--------------------------+---------+------------+----+
| cid | name | type                     | notnull | dflt_value | pk |
+-----+------+--------------------------+---------+------------+----+
| 0   | C2   | integer generated always | 0       | --         | 0  |
+-----+------+--------------------------+---------+------------+----+
</code>

Same for index "XC2":

<code>
select * from pragma_index_list('T')
+-----+------+--------+--------+---------+
| seq | name | unique | origin | partial |
+-----+------+--------+--------+---------+
| 0   | XC2  | 0      | c      | 0       |
+-----+------+--------+--------+---------+
select * from pragma_index_xinfo('XC2')
+-------+-----+------+------+--------+-----+
| seqno | cid | name | desc | coll   | key |
+-------+-----+------+------+--------+-----+
| 0     | 2   | C2   | 0    | BINARY | 1   |
| 1     | -1  | --   | 0    | BINARY | 0   |
+-------+-----+------+------+--------+-----+
</code>

... and for foreign keys:

<code>
select * from pragma_foreign_key_list('T')
+----+-----+-------+------+----+-----------+-----------+-------+
| id | seq | table | from | to | on_update | on_delete | match |
+----+-----+-------+------+----+-----------+-----------+-------+
| 0  | 0   | S     | C2   | -- | NO ACTION | NO ACTION | NONE  |
+----+-----+-------+------+----+-----------+-----------+-------+
</code>

It would be nice to see it fixed in a future release!

Thanks

(2) By Richard Hipp (drh) on 2020-06-25 15:41:17 in reply to 1.0

> Generated column "C2" does not appear in system table table_info('T'):

Please try using table_xinfo('T') instead.  That seems to work for me.

(3) By J-L Hainaut (JLHainaut) on 2020-06-25 16:02:00 in reply to 2 [link]

You are right. It works.

Thanks

(4) By midijohnny on 2023-03-17 21:41:11 in reply to 3 [link]

👍