1

I have data stored in a Redshift cluster.

I have several tables that I want to join based on a dynamic query.

table_1
table_2
table_3
table_4
table_5

and the following table which contains meta information about the other tables that I use to filter them.

table_metadata

Basically, I'd like to query the table table_metadata to get a list of tables to UNION (based on some filters) and then create an other table base on the dynamic union.

For instance, let's say the query:

 select tablename from table_metadata where last_update='2018-06-25';

returns

 table_2
 table_3
 table_5

Then, I'd like to be able to run something which will be the equivalent of:

 CREATE TABLE new_table AS (
 SELECT * FROM table_2
 UNION
 SELECT * FROM table_3
 UNION
 SELECT * FROM table_5
 );

Is this possible to do all of this in Postgresql directly?

I tried to look at UDFs, but could not quite figure it out.

Thanks a lot for your help!

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
PGuiv
  • 537
  • 5
  • 11
  • 1
    you cannot do that in redshift - please edit title and text as postgres <> redshift. you would have to generate the query outside redshift e.g. in an etl tool/python/glue. The other option is to rethink your reasons for having separate tables in the first place. – Jon Scott Jun 26 '18 at 16:29

0 Answers0