CODEWITHSUNDEEP
sqlamazon-redshift
DevBabai
DevBabai

Reputation: 83

How do I export column names to a file in redshift

I have been trying to export column names from postgres/redshift table to a file using below query:

COPY (select * from ios.rating where false) 
TO '/Users/xxx/ios-rating-file.csv' DELIMITER ',' CSV HEADER;

is there a way I could export column names from redshift either to file or just in the IDE?

Upvotes: 0

Views: 1876

Answers (2)

Kt Mack
Kt Mack

Reputation: 381

select * from svv_all_columns

svv_all_tables will give you additional information like whether it is a view or a table.

https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_ALL_COLUMNS.html

Upvotes: 0

Tom
Tom

Reputation: 1133

To get all column names of a table, you can use the pg_table_def Table in Redshift.

For example, to extract all column names of a table:

SELECT "column",
FROM pg_table_def
WHERE tablename = 'YOUR_TABLE_NAME'

Regarding your query - the Redshift COPY command is used to insert data into Redshift. To export data from Redshift, you need to use the UNLOAD command to write files to S3. You can read more here: AWS docs Redshift Unload

If you have an IDE like Datagrip you can run the query above on the "pg_table_def" table and just copy/paste the column names manually if it's a one time usage.

Upvotes: 1

Related Questions