dynamic table name in redshift -

i have few tables similar names different prefixes: us_cities, ca_cities , uk_cities. each 1 of tables consist 1 column (city_name). want union tables , result that:

select 'us' country, city_name us_cities union select 'ca' country, city_name ca_cities union select 'uk' country, city_name uk_cities 

in future have more city tables more countries , want dynamic query/view identify relevant tables (*_cities) , add them in union query. how can in redshift?

you can use information_schema.tables table list of tables match naming convention. need external process replace view.

select * information_schema.tables table_name '%_cities'; 

imho, you'd better off having single cities table , using views create country specific versions. :)