AWS / Azure / GCP Cloud | Database | Data Analytics | LakeHouse | Machine Learning | Shrenik Parekh

Migrate Green Plum Array Data Type To Redshift IN clause: Green Plum: select * from table_t1 where col_1= ANY ARRAY ('A','B',C'); Redshift: select * from table_t1 where col_1 IN ('A','B','C')

Green plum distinct on To Redshift row_number: Green Plum: select distinct on (col_1, col_2, col_3) from table_t1 where condition_1=some_condition order by col_1, col_2, col_3 Rdshift: select col_1, col_2, col_3 as <alias> from      (      select row_number() over     (      partition by col_1 order by col_1      ) as rn, col_1, col_2, col_3      from table_1      where col_4=<condition>      order by col_1, col_2, col_3      ) as t1 where t1.rn=1 Note: If you include more columns for partition by clause, check the no. of row count, as it should be different.

date_part to datediff: Green Plum: select ( date_part ('second' :: text, t2.transaction_date - t1.transaction_date) ) / (60 * 24 * 60) :: double precision as GP_time from Table_T1 where t1.tra_id=t2.tran_id Redshift: select DATEDIFF ( 'second', t1.transaction_date::timestamp, t2.transaction_date::timestamp )/ (60 * 24 * 60) :: double precision as RS_time from Table_T1 where t1.tra_id=t2.tran_id   Attention: Pay attention to the removal of minus sign while using DATADIFF function for Redshift, order of date column such as 2nd column will be at 1st position and vice versa, number of argument is 3 - second / minute / hour / day, date1, date2. Green Plum date_part function has 2 argument compared to Redshift DATEDIFF function.