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 [source]

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:

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;

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

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

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

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

Same for index "XC2":

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 | +-------+-----+------+------+--------+-----+

... and for foreign keys:

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 | +----+-----+-------+------+----+-----------+-----------+-------+

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 [link] [source]

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] [source]

You are right. It works.

Thanks

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

👍