select distinct(table_name) from svv_all_columns ;
SELECT distinct(source_table_name) FROM "ctrl_stg_cdr_rules" order by source_table_name ;
I want to take intersection for the above two queries but getting this error
ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables. [ErrorId: 1-63eb4c35-4b45b94c02210a19663d78db]
SELECT table_name FROM svv_all_columns WHERE database_name = 'singh_sandbox' AND schema_name = 'new_sources'
INTERSECT
SELECT source_table_name FROM ctrl_stg_cdr_rules
ORDER BY table_name;
I am expected to get list of all missing tables
Oh this error again. This has to be one of the worst written error messages in existence. What this means is that you are trying to use leader-node only data in a query being run on the compute nodes.
You see Redshift is a cluster with a leader node that has a different purpose than the other nodes (compute nodes). When a query runs the compute nodes execute on data they have direct access to, then the results from the compute nodes is passed to the leader for any final actions and passed to the connected client. In this model the data only flow one way during query execution. This error happens when data only accessible from the leader node is needed by the compute nodes - this includes results from leader-only functions and/or leader-node only tables. This is what is happening when you perform INTERSECT between these two selects.
To resolve this you need to produce the leader-only data as a separate select and route the data back to the compute nodes through a supported process. There are two classes of methods to do this - have an external system route the data back OR use a cursor and route the results back. I wrote up how to perform the cursor approach in this answer: How to join System tables or Information Schema tables with User defined tables in Redshift
The bottom line is that you cannot do what you intend simply because of the architecture of Redshift. You need a different approach.