- Pivotal Greenplum 4.2.x
- OS All Supported Versions
Creating an external table for a file with "ESCAPE" set to "OFF" produces the following error:
"Error: Escape in CSV format must be a single character"
For example, using the following syntax reproduces this issue:
CREATE EXTERNAL TABLE test(a character varying(8)) ('gpfdist://localhost:8081/data/test.csv') FORMAT 'CSV' (DELIMITER E'\t' ESCAPE 'OFF');
ESCAPE 'OFF' is only supported for TEXT format. It is not supported for CSV format.
Replace the CSV format to TEXT.
CREATE EXTERNAL TABLE test(a character varying(8)) ('gpfdist://localhost:8081/data/test.csv') FORMAT 'TEXT' (DELIMITER E'\t' ESCAPE 'OFF');
ESCAPE specifies the single character that is used for C escape sequences (such as \n,\t,\100 and so on) and for escaping data characters that might otherwise be taken as row or column delimiters. The default escape character is a \ (backslash) for text-formatted files and a " (double quote) for CSV-formatted files. However, it is possible to specify another character to represent an escape. It is also possible to disable escaping in text formatted files by specifying the value 'OFF' as escape value. This is very useful for data such as text formatted web log data that has many embedded backslashes that are not intended to be escapes.