I am trying to replicate a functionality from SQL Server into redshift where I have to ignore column if the column exists, otherwise add it into the table.
I have come across these posts in stackoverflow, however couldn't find a proper solution from them:
-
https://stackoverflow.com/questions/65103448/redshift-alter-table-if-not-exists
-
https://stackoverflow.com/questions/42035068/redshift-add-column-if-not-exists
-
https://stackoverflow.com/questions/42669237/workaround-in-redshift-for-add-column-if-not-exists
I am able to get a TRUE or FALSE for columns that I want to check. But I don't know how to ALTER the table to add or remove one. I am also a bit new to redshift, so I would appreciate a response!
These are some of my attempts:
IF (SELECT EXISTS(SELECT * FROM pg_catalog.pg_table_def
WHERE schemaname = 'my_schema'
AND tablename = 'my_table'
AND "column" = 'my_new_column'
)) <> TRUE
THEN
ALTER TABLE my_table
ADD COLUMN my_new_column varchar
END IF;
CREATE OR REPLACE PROCEDURE if_else()
LANGUAGE plpgsql
AS $$
BEGIN
IF (SELECT EXISTS(SELECT * FROM pg_catalog.pg_table_def
WHERE schemaname = 'my_schema'
AND tablename = 'my_table'
AND "column" = 'my_new_column'
)) <> TRUE
THEN
ALTER TABLE my_table
ADD COLUMN my_new_column varchar
END IF;
END;
$$
;
CALL if_else();
A few more failed attempts:
CREATE OR REPLACE PROCEDURE alter_my_table()
AS $$
BEGIN
ALTER TABLE my_table
ADD COLUMN my_new_column varchar
END;
$$
LANGUAGE plpgsql
;
SELECT
CASE WHEN COUNT(*) THEN 'warning: column exists already.'
ELSE CALL alter_my_table();
END
FROM pg_catalog.pg_table_def
WHERE schemaname = 'my_schema'
AND tablename = 'my_table'
AND "column" = 'my_new_column'
Thank you for your time.
thank you Milind! This should work :)