COPY FROM "STDIN REJECTED DATA AS TABLE

Sanchay_Gupta

I am unable to execute the "COPY tablename. FROM "STDIN REJECTED DATA AS TABLE tablename_rejects from Java 1.8 to a remote vertica server(docker running Vertica Analytic Database v9.0.0-0). Is there any workaround?

ThePebble

What error are you getting? Looks like there are typos in your statement. Look at the quotes and the misplaced period after tablename.

A good debugging tip is to try the sql in vsql:
[dbronson@MetaPebble:vtest /tmp 20ms]$ vsql -c "create table s (i int);"
CREATE TABLE
[dbronson@MetaPebble:vtest /tmp 40ms]$ vsql -c "copy s. from STDIN rejected data as table s_rej;"
ERROR 2754: COPY requires a data source; either a FROM clause or a WITH SOURCE for a user-defined source
[dbronson@MetaPebble:vtest /tmp (1) 6ms]$ vsql -c "copy s from STDIN rejected data as table s_rej;"
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> .
[dbronson@MetaPebble:vtest /tmp 6.3s]$

eugene_rozman

Hi - tried that, but having trouble terminating STDIN. Neither backslash with a dot, or a single dot as shown above, work - STDIN continues feeding blank lines. Any thoughts? Thank you

Follow up: that was solved by removing run options (I had -AXtnqi), and with the defaults, . does terminate STDIN.

mosheg

Try if one of the following options work for you.

vsql -ec "create table mytable (f1 int);"
printf "1\n2\n3\n" | vsql -ec "copy mytable from STDIN rejected data as table mytable_rej abort on error;"
printf "4\n5\n6\n\.\n" | vsql -ec "copy mytable from STDIN rejected data as table mytable_rej abort on error;"
printf "7\n8\n9\n" > my_input_file
vsql -ec "copy mytable from STDIN rejected data as table mytable_rej abort on error;" < ./my_input_file
vsql -ec "select * from mytable order by 1;"