Dynamic data used as column name in Amazon Redshift

Question

I am trying to use a year-month dynamic function to use it as a column name in Amazon Redshift. I don't want to write a hardcode for that. The database tool I'm using is DBeaver Ultimate and the code is the following:

create table test diststyle all 
as (
 select 
 psid
 ,count(case when year_month = convert(bigint, to_char(date_trunc('month', current_date) - interval '14 month', 'yyyymm')) then psid else null end) as to_char(date_trunc('month', current_date - interval '14 month'), 'yyyymm') **--column I want**
 ,count(case when year_month = to_char(date_trunc('month', current_date) - interval '13 month', 'yyyymm') then psid else null end) as "202202" --hardcode
 from tb_app_opened_globoplay_redu
 group by psid
 );

Can you please help me with that?

Answer 1

To make a dynamically named column name you will need to use dynamic SQL - a SQL statement defined as a character string in code and then executed. I have provided an example of how to do that below, but I would caution that this is a problematic solution. For example downstream code generally needs to know what columns exist and what their names are. This is especially true if you plan to replace the previous name with a new name because the downstream code will fail if the column name doesn't exist (yet). This is less true if you are adding a new column for each month, but I would still then recommend a different architecture. For example, your dynamically named column could be replaced with a column named "as_of_month" where you simply include which month you care about in the WHERE clause of your query - for example: select * from test where as_of_month = '202303'; Not knowing your exact use case, perhaps that would be: create table temp as select to_char(year_month,'YYYYMM') as as_of_month, count(psid) from tb_app_opened_globoplay_redu group by to_char(year_month,'YYYYMM'); where you can then query for your character formatted year month of 13 months ago. Back to the original request, please also of note that using an entirely numeric column name will require the use of double quotes every time you reference the column and mistakes by users regarding this will assuredly occur and cause their own confusion.

Caveats and recommendations aside, here is a very quick implementation that should be close to what you specifically asked about.

create or replace procedure public.rebuild_test_table()
language plpgsql as $$
declare
 v_column_name varchar(6) := quote_ident(to_char(date_trunc(''month'', current_date - interval ''14 month''), ''yyyymm'')); --quote_ident() will add double quotes.
 v_dynamic_sql varchar(5000);
begin
 v_dynamic_sql = 
 'create table test diststyle all 
 as (
 select 
 psid
 ,count(case when year_month = convert(bigint, to_char(date_trunc(''month'', current_date) - interval ''14 month'', ''yyyymm'')) then psid else null end) as '+v_column_name+'
 ,count(case when year_month = to_char(date_trunc(''month'', current_date) - interval ''13 month'', ''yyyymm'') then psid else null end) as "202202" --hardcode
 from tb_app_opened_globoplay_redu
 group by psid
 );';
 execute 'drop table if exists test';
 execute v_dynamic_sql;
end;
$$;