The Vertica Database Forums

Posted: **Mon Nov 14, 2016 9:13 pm**

I'm curious ... How come a table's schema is identified by the TABLE_SCHEMA column in TABLES system table, while a table's schema name in the ALL_TABLES system table is identified by the SCHEMA_NAME column?

Code: Select all

dbadmin=> select distinct table_schema from tables;
 table_schema
--------------
 test
 public
 pulse
(3 rows)

dbadmin=> select distinct table_schema from all_tables;
ERROR 2624:  Column "table_schema" does not exist

dbadmin=> select distinct schema_name from all_tables;
 schema_name
-------------
 pulse
 v_catalog
 test
 public
 v_monitor
(5 rows)

Posted: **Thu Nov 17, 2016 8:48 pm**

Hi Beth,

Not sure why this is the case, but it is frustrating sometimes trying to remember which columns are in which tables.

One work around could be to create your own system views in the PUBLIC schema and add the columns you prefer...

Example:

Code: Select all

dbadmin=> select min(schema_name) from all_tables;
 min
-----
 jim
(1 row)

dbadmin=> select min(schema_name) from tables;
ERROR 2624:  Column "schema_name" does not exist

dbadmin=> create view public.tables as
dbadmin-> select
dbadmin-> table_schema_id,
dbadmin-> table_schema schema_name,
dbadmin-> table_id,
dbadmin-> table_name,
dbadmin-> owner_id,
dbadmin-> owner_name,
dbadmin-> is_temp_table,
dbadmin-> is_system_table,
dbadmin-> force_outer,
dbadmin-> is_flextable,
dbadmin-> has_aggregate_projection,
dbadmin-> system_table_creator,
dbadmin-> partition_expression,
dbadmin-> create_time,
dbadmin-> table_definition,
dbadmin-> recover_priority,
dbadmin-> storage_mode
dbadmin-> from v_catalog.tables;
CREATE VIEW

dbadmin=> select min(schema_name) from tables;
 min
-----
 jim
(1 row)

The Vertica Database Forums

System table's column names are not uniform???

System table's column names are not uniform???

Re: System table's column names are not uniform???