Check if Column Exists and drop using Vertica

fWe have a HP Vertica database and I am writing some scripts that will be eventually deployed in GIT to make changes to my dimensional model. As a part of general house keeping in the first few lines I will have some code that will remove the elements being modified in the script which all seems very straigh forward.

Here is an example of the sort of thing I am using, and it works.

DROP CONSTRAINT if exists fk_fctClick_Money_SourceKey


DROP TABLE if exists SCHEMA.dimSourceType

I am a bit stuck on how to achieve this code when checking to see if a column exists. Seems straighforward in most other database's but I cannot find anything on how to do this in Vertica.

I know in PostgreSQL it looks a bit like this:

ALTER TABLE SCHEMA.dimSourceTypeS DROP COLUMN IF EXISTS SourceKey;

This doesn't work as the error says there is an error near word "EXISTS". The MSSQL counterparts does not work either.

Can anyone help me on this query please?

Solution

You can not use "IF EXISTS" since it is not supported. Pl see doc here

You can loop through the columns of table of interest or each table and run alter table on existing columns only.

With something like below using your favorite language:

for a given table
   set_of_existing_cols = get("SELECT COLUMN_NAME FROM columns WHERE table_name = <YOUR_TABLE>")
   set_of_delete_columns = set_of_existing_columns intersect set_of_columns_you_want_to_delete
   for each column from set_of_delete_colmn
      run("ALTER TABLE <YOUR_TABLE> DROP COLUMN column")