How to Export a Redshift Table to CSV in PostgreSQL

Galaxy Glossary

How do I export a Redshift table to CSV?

UNLOAD or \copy lets you dump a Redshift table to a CSV file for easy sharing or downstream processing.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why export a Redshift table to CSV?

CSV files are portable, human-readable, and accepted by most BI tools. Exporting lets you archive data, debug queries locally, or feed machine-learning pipelines without granting live database access.

Which Redshift commands can create a CSV?

Inside Redshift you can use UNLOAD to push data directly to Amazon S3.From outside, the \copy meta-command in psql streams rows to your laptop or a server.

When should I use UNLOAD versus \copy?

Use UNLOAD for large tables, scheduled jobs, or cross-region transfers because it writes in parallel to S3. Use \copy for ad-hoc extracts under a few million rows when you already have a psql session open.

How do I export using UNLOAD?

Grant the cluster an IAM role with s3:PutObject.Choose a unique S3 URI and run the syntax below. Redshift writes multiple gzipped CSV parts by default.

Example: export Customers table

UNLOAD ('SELECT id,name,email,created_at FROM public.customers') TO 's3://my-bucket/exports/customers_' CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/redshift-s3-access' DELIMITER ',' ADDQUOTES NULL AS 'NULL' ALLOWOVERWRITE PARALLEL OFF;

How do I export using psql \copy?

Open a terminal on a machine with network access to Redshift. Run psql with -h, -U, and -d.Then issue \copy to stream the result set to a local CSV.

Example: export Orders table

\copy (SELECT * FROM public.orders ORDER BY order_date) TO 'orders_2024-04-23.csv' CSV HEADER

Best practices for clean exports

Always quote text fields to protect embedded commas. Specify NULL AS so downstream tools can distinguish real blanks. Turn PARALLEL OFF when order matters in the file.

How can I verify the export?

After UNLOAD, list the target S3 prefix and spot-check one part file.After \copy, count lines with wc -l and compare to SELECT COUNT(*) from the original table.

What if my table has sensitive data?

Filter columns in the SELECT list, encrypt the S3 bucket, and restrict IAM roles. For psql, write to an encrypted disk or tunnel through SSH.

Why How to Export a Redshift Table to CSV in PostgreSQL is important

How to Export a Redshift Table to CSV in PostgreSQL Example Usage


-- Dump high-value orders to S3
UNLOAD ('SELECT o.id, c.email, o.total_amount
        FROM orders o
        JOIN customers c ON c.id = o.customer_id
        WHERE o.total_amount > 500')
TO 's3://company-exports/high_value_orders_'
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/redshift-s3-access'
DELIMITER ',' ADDQUOTES GZIP;

-- Ad-hoc local export of low-stock products
\copy (SELECT id, name, stock FROM products WHERE stock < 10) TO 'low_stock.csv' CSV HEADER

How to Export a Redshift Table to CSV in PostgreSQL Syntax


UNLOAD ('<SELECT-statement>')
TO '<s3_uri_prefix>'
CREDENTIALS 'aws_iam_role=<role_arn> | aws_access_key_id=<key>;aws_secret_access_key=<secret>'
[DELIMITER ',' | '\t'] [ADDQUOTES] [NULL AS '<null_string>']
[ESCAPE] [ALLOWOVERWRITE] [PARALLEL { ON | OFF }]
[GZIP | BZIP2] [HEADER]

\copy (<SELECT-statement>) TO '<file_path>' [CSV] [HEADER] [DELIMITER ',' | '\t'] [NULL '<null_string>']

Common Mistakes

Using UNLOAD without an IAM role: Redshift cannot write to S3 unless you attach a role or access keys. Fix: create a role with s3:PutObject and add it to the cluster.
Forgetting ADDQUOTES with text columns: unquoted commas break CSV structure. Fix: append ADDQUOTES or wrap the SELECT with REPLACE on commas.