Redshift Alter table if not exists

Question

I'm trying add a new field in a redshift table. But I want to add only if this field doesn't exists. I tried wrapping it with IF NOT EXISTS. But I got following error: Amazon](500310) Invalid operation: syntax error at or near "IF" Position: 5;

BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'schema_name' and table_name='table_name' and column_name='new_field') THEN 
    ALTER TABLE schema_name.table_name
    ADD new_field INT;
  END IF;

COMMIT;

I'm not sure if I'm correctly using "IF NOT EXISTS" statement inside the BEGIN block. Can someone please help me?

Thanks in advance!

Did you got any solution for this? – Athi Jan 25 '22 at 15:06 — Athi, Jan 25 '22 at 15:06

score 0 · Answer 1 · answered Dec 02 '20 at 07:33

0

It could be better to handle it using EXCEPTION

BEGIN
    ALTER TABLE
        <table_name> ADD COLUMN <column_name> <column_type>;
    EXCEPTION
        WHEN duplicate_column
            THEN RAISE NOTICE 'column <column_name> already exists in <table_name>.';
END;

answered Dec 02 '20 at 07:33

Yauheni Khvainitski

111
10

We use such a solution in production at our project – Yauheni Khvainitski Dec 02 '20 at 07:34
1

Is this solution works in Redshift? seems not. – Athi Jan 25 '22 at 15:00

score 0 · Answer 2 · answered Jul 15 '22 at 15:17

The Answer by Yauheni Khvainitski is not completely wrong. But you do have to use a SP and the only option Redshit has (at this point is to have "EXCEPTION WHEN OTHER"). An example:

CREATE OR REPLACE PROCEDURE change_column_to_big_int_TABLE_NAME_X(column_name varchar(200)) AS
$$
DECLARE
  new_column_name VARCHAR;
BEGIN
 SELECT INTO new_column_name (table_name)||'_new';
-- RAISE INFO 'new_table_name = % table_name = %',new_column_name, table_name;
 ALTER TABLE TABLE_NAME_X ADD COLUMN "(new_column_name)" bigint;
 EXCEPTION WHEN OTHERS
 THEN RAISE NOTICE 'column already exists on table';
END;
$$
LANGUAGE plpgsql;
    
CALL change_column_to_big_int_TABLE_NAME_X('COLUMN_Y');

Some links from AWS on:

trapping errors: https://docs.aws.amazon.com/redshift/latest/dg/stored-procedure-trapping-errors.html
CREATE PROCEDURE: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_PROCEDURE.html

Also please notice that this is valid at this point in time. Redshift seems to be always evolving.

score 0 · Answer 3 · answered Jan 05 '23 at 19:16

0

The issue I think is that AWS Redshift does not support the IF statement, but instead uses CASE statements. The CASE statements are very similar to IF the way they implement them. But I admit, I prefer the IF statements.

answered Jan 05 '23 at 19:16

Greg Davis

1
1

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/33569183) – André Kugland Jan 07 '23 at 07:19

Redshift Alter table if not exists

3 Answers3

Linked