1

I want to do something like

DROP PROJECTION IF EXISTS myProjection;

Apparently I can use IF EXISTS for a table but not a projection.

I understand if I drop the table with CASCADE, that should drop the corresponding projections, but I apparently have some orphaned projections which I do not have a track of. And sometimes when I rename tables/projections, it fails saying projection already exists.

The Drop Projection page and this stackoverflow page for generic sql do not help much.

Edit: I am using this in Mybatis Migrations. So my entire migration would fail if there is an error in any of the scripts. So, no I can not ignore the error.

If there is no IF EXISTS for a projection -- is there a programatic way (LIKE TSQL/PLSQL) where I could specify a condition to check if the projection exists and take an action whether to drop it or not?

Community
  • 1
  • 1
Gadam
  • 2,674
  • 8
  • 37
  • 56
  • You can check the system view `projections` for the presence of a projection –  Jan 28 '16 at 20:29
  • https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/GET_PROJECTION_STATUS.htm – Hogan Jan 28 '16 at 20:32
  • @Hogan GET_PROJECTION_STATUS would throw an error if the projection does not exist. I just confirmed it my running: SELECT GET_PROJECTION_STATUS('myProjection'); – Gadam Jan 28 '16 at 21:12
  • @a_horse_with_no_name Considering that there is no If statement in vertica, I am not sure how to exactly frame the script. I tried with the [case statement](https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/LanguageElements/Expressions/CASEExpressions.htm) but did not seem like the way to go. – Gadam Jan 28 '16 at 21:19
  • Can you provide a bit more details about your process? Why don't you simply issue a `DROP` and ignore the error? – mustaccio Feb 03 '16 at 19:52
  • @mustaccio: My process is a mybatis migration script, which will fail the entire migration if there is an error in the script. You say I can 'ignore' the error, do you mean programmatically (like a 'ignore' command)? – Gadam Apr 26 '16 at 20:16

3 Answers3

2

There is no drop projection IF EXISTS .... You can just use drop projection ... and - of course - you will get an error message if the projection you're trying to delete does not exists.

You can list ALL projections for a given schema/table using a SQL like this:

\set schema '''my_schema'''
\set table '''my_table'''
select
    projection_name,
    sum(row_count) as row_count, 
    sum(used_bytes) as used_bytes,
    sum(wos_row_count) as wos_row_count, 
    sum(wos_used_bytes) as wos_used_bytes, 
    sum(ros_row_count) as ros_row_count, 
    sum(ros_used_bytes) as ros_used_bytes
from
    projection_storage 
where 
    anchor_table_schema = :schema and
    anchor_table_name = :table
group by 1
order by 1
;

And the following will list all projections associated with tables in a given schema:

\set schema '''my_schema'''
select
    projection_name,
    anchor_table_name,
    sum(row_count) as row_count, 
    sum(used_bytes) as used_bytes,
    sum(wos_row_count) as wos_row_count, 
    sum(wos_used_bytes) as wos_used_bytes, 
    sum(ros_row_count) as ros_row_count, 
    sum(ros_used_bytes) as ros_used_bytes
from
    projection_storage 
where 
    anchor_table_schema = :schema
group by 1, 2
order by 1, 2
;
mauro
  • 5,730
  • 2
  • 26
  • 25
1

you can query catalog table in vertica, this will give you schema names of all present schemas ,

select v_catalog.columns from v_catalog.columns;

i sometime use this select query , which gives me evey info related to a table

select export_objects('','your_schema.table_name');
aryan singh
  • 151
  • 11
0

This is possible since Vertica 9.2.x.

DROP PROJECTION IF EXISTS myProjection;

Reference: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/DROPPROJECTION.htm

sudeep
  • 735
  • 1
  • 9
  • 8