CODEWITHSUNDEEP
sqlamazon-redshift
logan
logan

Reputation: 8346

Could not load data from information_schema.columns to another physical table

I tried simple create statement from load from information_schema.columns, it did work.

CREATE TABLE mydb.stg_information_schema_columns
AS
SELECT CAST(column_name AS TEXT) FROM information_schema.columns

Amazon Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.;

So I Manually created a table and tried to insert

INSERT INTO mydb.stg_information_schema_columns 
SELECT CAST(column_name AS TEXT) FROM information_schema.columns

got the same ERROR

But the plain SELECT statement works.

SELECT CAST(column_name AS TEXT) FROM information_schema.columns
--> this works

Any help would appreciate !

Upvotes: 1

Views: 776

Answers (1)

Jon Scott
Jon Scott

Reputation: 4354

Redshift has a LEADER node and COMPUTE nodes

Certain features are only available on the leader node, this includes access to the information_schema tables.

All "normal" schema tables are only available on COMPUTE nodes.

When you try to mix the two, for example by creating a normal table from a leader node only table, it does not work.

The same applies to all leader only functions and tables.

The work around may be to use an external process like a python script to get the data from the information_schema table then if needed load that data to a normal table.

Upvotes: 4

Related Questions