The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Vertica client driver: Load data to temp table

I am using Vertica client driver version 8.1 to save data to temptable using the following query :
COPY TempTableName FROM STDIN DIRECT DELIMITER '|' RECORD TERMINATOR '\r\n' NO ESCAPE NO COMMIT;
while using this statement to add data to temp table I get a error if I have used "|" in the data to saved.
One of the ways to fix this is using ENCLOSED BY paramater like :
COPY TempTableName FROM STDIN DIRECT DELIMITER '|' RECORD TERMINATOR '\r\n' ENCLOSED BY '"' NO ESCAPE NO COMMIT;
But in this case if I used " in the data to be saved it leads to an error. This there are a generic way to handle this and insert all possible characters ('",.;/:?~!@#$%^&*|\/) on keyboard while saving.
Tagged:
0
Answers
Your situation is the reason my friend Maurizio Felici avoids using printable characters (commas, new-lines,…) as Field/Record separators
See: https://www.linkedin.com/pulse/vertica-vsql-tips-tricks-maurizio-felici/
Check the "Exporting data to file (the safe way)" section.
Is it possible to have the data include an "ASCII value of 1 (CTRL-A)" as the field separator and an "ASCII value 2 (CTRL-B)" for the record separator?
It'll be much easier to load!
Thanks!