Handling COPY FROM errors when loading with Wildcards


When using wildcards with COPY FROM, I am having difficulty with handling copy errors.

When I run this command:

COPY myTable FROM '/mydirectory/*.dat' ON ANY NODE
EXCEPTIONS '/copy_errors/exceptions/'
REJECTED DATA '/copy_errors/rejected/';

This works if /mydirectory/ has multiple files in them.

But if it is a single file, I get an error:

ERROR: COPY: Could not open rejected data file [/copy_errors/rejected/] for writing: Is a directory.

If I change things around and specify a file instead, the copy picking up multiple files from the directory would break.

This is a huge pain. Is there any workaround other than checking the number files to be picked up before issuing a copy command?

Thank you,

PS.: The preview functionality of this message board is keep timing out!!! It took me 5 minutes to post this message


  • Prasanta_PalPrasanta_Pal - Select Field - Employee
    You can write a shell script ( .sh file ) and use for example
    vsql -w<password> -c "COPY test from '/home/dbadmin/export.dat' DELIMITER E'\001' RECORD TERMINATOR E'\003' DIRECT" in the script)  for two choices i) if the number of files is one and 2 ) if the number of files >1

  • FYI an alternative here is to pass some dummy file as an additional argument. Vertica doesn't mind that its empty and it forces vertica to always use the multiple files logic.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file