List projections in Vertica database

Query below lists all projections and their table in Vertica database.

Query

select projection_schema,
       projection_name,
       anchor_table_name as table_name,
       case when is_up_to_date then 'YES'
            else 'NO' end as is_up_to_date,
       case when is_prejoin then 'YES'
            else 'NO' end as is_prejoin,
       case when is_super_projection then 'YES'
            else 'NO' end as is_super_projection,
       case when is_aggregate_projection then 'YES'
            else 'NO' end as is_aggregate_projection
from v_catalog.projections
order by projection_schema,
         projection_name;

Columns

  • projection_schema - schema name of hte projection
  • projection_name -projection name
  • table_name - table name on which projection was created ( FROM clause)
  • is_up_to_date - indicate if data in projection is up to date
  • is_prejoin -indicate if projection is prejoin projection
  • is_super_projection - indicate if projection is super projection (contains all columns from anchor table)
  • is_aggregate_projection - indicate if projection is aggregate projection (contains one of aggregate function)

Rows

  • One row represents one projection in the database
  • Scope of rows: all projections in the database
  • Ordered by schema and projection name

Sample results

0
There are no comments. Click here to write the first comment.