Incorrect output when exporting AWS Redshift data to S3 using UNLOAD command

Question

I'm not a database guy and i have just started exploring Redshift. I created a cluster and used their sample scripts to populate tables. I want to copy data from Redshift to Postgres. I first wanted to export the data to S3 and then import into Postgres. After i ran the following command, a file got generated in my S3 bucket:

UNLOAD ('select * from date')
TO 's3://sample-dwh-data/date_' credentials
'aws_access_key_id=******;aws_secret_access_key=*************'
PARALLEL OFF;

This is the script i used to create date table in Redshift:

create table date(
    dateid smallint not null distkey sortkey,
    caldate date not null,
    day character(3) not null,
    week smallint not null,
    month character(5) not null,
    qtr character(5) not null,
    year smallint not null,
    holiday boolean default('N'));

Problem is that when i open the exported file present in S3, i see the last column for each row has values either set as f or t. Sample output from exported file:

2070|2008-09-01|MO|36|SEP|3|2008|t
2071|2008-09-02|TU|36|SEP|3|2008|f

However, if i query the Redshift table, last column value is either true or false. So, why is the UNLOAD command trimming the last column's value?

UPDATE: @Yankee: When i ran the following command: 'SELECT dateid,caldate,day,week,month,qtr,year,CASE WHEN holiday = true THEN 'TRUE' ELSE 'FALSE' END AS holiday from date', i got this error:

An error occurred when executing the SQL command:
UNLOAD ('SELECT dateid,caldate,day,week,month,qtr,year,CASE WHEN holiday = true THEN 'TRUE' ELSE 'FALSE' END AS holiday from date')
TO 's3://sample-dwh-data/date_...

[Amazon](500310) Invalid operation: syntax error at or near "TRUE" 
Position: 87;

UNLOAD ('SELECT dateid,caldate,day,week,month,qtr,year,CASE WHEN holiday = true THEN 'TRUE' ELSE 'FALSE' END AS holiday from date')
                                                                                      ^                                                                                
1 statement failed.

why do you think this is a problem? the unload command uses t and f to represent boolean. the copy command would accept these as valid values. — Jon Scott
The problem is that i want to load the exported data to a Postgres instance running on Heroku. I'm not sure whether this will change f to false or t to true while importing it to another database (in my case, Heroku Postgres). Also, is this common for UNLOAD operation to trim the output in case of boolean values? — Technext

Yankee Yankee · Accepted Answer · 2018-04-23T09:36:53

Instead of doing a SELECT * FROM DATE, you can specify the column names and there you can customize how you want the data to be exported, by using CASE IF ELSE conditions.

SELECT dateid,caldate,day,week,CASE WHEN holiday = true THEN \'TRUE\' ELSE \'FALSE\' END AS holiday from date;

Incorrect output when exporting AWS Redshift data to S3 using UNLOAD command

1 Answers