Redshift : Based on whether column exists in table_1, create another table and assign column values

Question

I have a table in Redshift named 'table_1', with columns named 'column_name_1','column_name_2','column_name_3'.......'column_m' etc.

table_1
column_name_1   column_name_2   column_name_3   column_name_4   ....   column_name_m

I want to make a temporary table named 'temp_table' based on the following conditions:

Select the 3 columns named 'column_name_1','column_name_2','column_name_3'

which I am sure already exist in 'table_1'.

Select column named 'column_n' which I am not sure exists in 'table_1'. If 'column_n' exists in 'table_1', select it as it is, if it does not exist, then make a column in 'temp_table' named 'column_n' and assign it a character value 'val_n' all the rows.

So my final 'temp_table' will look like this:

If 'column_n' exists in 'table_1'

temp_table

column_name_1  column_name_2   column_name_3   column_n
col_1_val_1    col_2_val_1     col_3_val_1     col_n_val_1
col_1_val_2    col_2_val_2     col_3_val_2     col_n_val_2
col_1_val_3    col_2_val_3     col_3_val_3     col_n_val_3
....           ....            ....            ....

If 'column_n' does not exist in 'table_1'

temp_table

column_name_1  column_name_2   column_name_3   column_n
col_1_val_1    col_2_val_1     col_3_val_1     val_n
col_1_val_2    col_2_val_2     col_3_val_2     val_n
col_1_val_3    col_2_val_3     col_3_val_3     val_n
....           ....            ....            ....

I tried to take some hints from this link : How can I test if a column exists in a table using an SQL statement, but somehow I have not been successful in testing for a condition inside creation of a temporary table.

score 1 · Answer 1 · answered May 17 '18 at 18:32

This is really tricky. Assuming you have an unique id column of some sort, you can do:

select t1.column1, t1.column2, t1.column3,
       (select column_n  -- not qualified on purpose
        from table1 tt1
        where tt1.id = t1.id
       ) as column_n
from table1 t1 cross join
     (select val_n as column_n) x;

This uses the scoping logic to resolve column_n in the subquery. If the column exists in table1, then the inner reference is used to fill the value. If not, the column from x is used. There is no danger of a conflict in this case, because table1 doesn't have the column.

score 0 · Answer 2 · answered May 18 '18 at 01:54

This is not something you should be attempting to do through SQL queries.

Instead, use System Tables and Views such as SVV_TABLES to retrieve information about tables, then construct the appropriate SQL statement to Select/Insert your data.

To clarify, the logic of figuring out which columns existing and which columns to insert/copy should be done outside of Amazon Redshift. Then, send the resulting commands to Redshift to interact with the data.

Redshift : Based on whether column exists in table_1, create another table and assign column values

2 Answers2