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

Moderator: NorbertKrupa

Post Reply
beth
Intermediate
Intermediate
Posts: 58
Joined: Tue Oct 01, 2013 12:42 pm

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

Post by beth » 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)

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1775
Joined: Sat Jan 21, 2012 4:58 am
Contact:

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

Post by JimKnicely » 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)
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica Database Administration”