Redshift UNLOAD command with extension parameter throws syntax error

Question

I am attempting to unload data from Redshift using the extension parameter to specify a CSV file extension. The CSV extension is useful to allow data files to be opened e.g. in spreadsheet software.

The command I run is:

```sql
unload ('select * from public.mytable') 
to 's3://mydomain/fZyd6EYPK5c/data_' 
iam_role 'arn:aws:iam::xxxxxxx:role/my-role' 
parallel off 
format csv 
extension '.csv.gz' 
gzip 
allowoverwrite;
```

This command throws an error message:

```
SQL Error [42601]: ERROR: syntax error at or near "extension"
```

It appears that the extension option is not recognized. I believe I have followed the official documentation and examples:

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

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

```
select version();

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.44903
```

I am testing the query from a Java application and from DBeaver.

Do I have a syntax error in my query? Could this be a Redshift bug? Replies appreciated.

Accepted Answer

Extension parameter is a feature released recently and it's available after version 1.0.45698. You are seeing this error since your cluster version 1.0.44903 is lower than this. Please wait until next maintenance window or try creating a new cluster/workgroup to get the updated version.

Cluster versions and released features are documented in following page. 
https://docs.aws.amazon.com/redshift/latest/mgmt/cluster-versions.html#cluster-version-173

> Adds a file extension parameter to the UNLOAD command, so file extensions are automatically added to filenames.

Also, UNLOAD will add a period between filename and extension automatically. So you don't need to specify `.` by yourself (ex: `extension '.csv.gz'` -> `extension 'csv.gz'`).

Redshift UNLOAD command with extension parameter throws syntax error

相關內容