boolean field in redshift copy

0
votes

I am producing a comma-separated file in S3 that needs to be copied to a staging table in a redshift database using the postgres COPY command.

It has one boolean field. With every sensible way I can think of to represent the boolean value in the file, redshift copy complains, usually with "Unknown boolean format".

I'm going to give up and change the staging table field to a smallint so that I can proceed with the copy and translate the value on the load from staging to the final redshift table, but I'm curious if anyone knows the correct incantation.

2
postgresqlamazon-redshift
Could you provide example data, the definition of the table, the COPY command, and any relevant rows from stl_load_errors? - Carl G

2 Answers

1
votes

A zero or one works just fine for us.

Check your loads carefully, it may well be another issue that's 'pushing' invalid data into your boolean column.

For instance, we had all kinds of crazy characters embedded in our data that would cause errors like that. I eventually settled on using the US character for the record separator.

0
votes

Check to make sure you're excluding the headers during the COPY command.

I ran into the same problem, but adding the ignoreheader 1 option (ignores 1 header line during import) solved the issue.