2

I'm trying to execute an S3 copy operation via Spark-Redshift and I'm looking to modify the Redshift table structure before running the copy command in order to add any missing columns (they should be all VARCHAR).

What I'm able to do is send an SQL query before running the copy, so ideally I would have liked to ALTER TABLE ADD COLUMN IF NOT EXISTS column_name VARCHAR(256). Unfortunately, Redshift does not offer support for ADD COLUMN IF NOT EXISTS, so I'm currently looking for a workaround.

I've tried to query the pg_table_def table to check for the existence of the column, and that works, but I'm not sure how to chain that with an ALTER TABLE statement. Here's the current state of my query, I'm open to any suggestions for accomplishing the above.

select 
    case when count(*) < 1 then ALTER TABLE tbl { ADD COLUMN 'test_col' VARCHAR(256) }
    else 'ok'
    end
from pg_table_def where schemaname = 'schema' and tablename = 'tbl' and pg_table_def.column = 'test_col'

Also, I've seen this question: Redshift: add column if not exists, however the accepted answer isn't mentioning how to actually achieve this.

Community
  • 1
  • 1
andreimarinescu
  • 3,541
  • 2
  • 25
  • 32
  • You can't do it purely from SQL. You'll need some application logic (outside of Redshift) to query the schema and then determine whether to call the ADD COLUMN command. – John Rotenstein Mar 08 '17 at 11:12
  • 1
    Thanks John. I've already begun going down this path while waiting for an answer, this just confirms the solution is correct :) – andreimarinescu Mar 08 '17 at 11:26
  • 1
    Hi @andreimarinescu, would you mind answering your own question if you happen to figured out the answer. I tried using the information from here https://dwgeek.com/redshift-conditional-statements-if-loop-while-for-continue-and-exit.html/ , but got no where. – AOE_player Mar 25 '22 at 19:39
  • 1
    @AOE_player I believe that I moved this logic to the Spark job, where I first query the table structure and then I issue a series of alter table commands if needed. That said, it's been about 5 years and I don't really remember the specifics. – andreimarinescu Apr 01 '22 at 15:33
  • Got it, I see. I appreciate your time for giving me a response :) – AOE_player Apr 04 '22 at 01:40

0 Answers0