COPY statement abort

Moderator: NorbertKrupa

Post Reply
bhaggyit
Newbie
Newbie
Posts: 10
Joined: Thu Apr 04, 2013 10:48 am

COPY statement abort

Post by bhaggyit » Wed Jun 25, 2014 10:16 am

Guys,
Could you please advise the way around to cause the COPY statement to reject the record instead of aborting it.



CREATE TABLE XXX_VOIP_DB_STAGE.TEMP
(DATETIME TIMESTAMP);
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COPY XXX_VOIP_DB_STAGE.TEMP (
TIME FILLER VARCHAR(10240)
,DATETIME AS TO_TIMESTAMP(TIME,'MMDDYYYY:HH24MI')
)
FROM LOCAL '/home/files/VOIP/test.csv' SKIP 1 DELIMITER ',' REJECTMAX 10000 NO COMMIT;

ERROR 2005: Invalid input for HH24: "d0"

cat /home/files/VOIP/test.csv
TIME
20140625:d0

Thank you!
-Bugs

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: COPY statement abort

Post by id10t » Wed Jun 25, 2014 5:15 pm

Hi!

[DELETED]
Last edited by id10t on Wed May 06, 2015 5:10 pm, edited 1 time in total.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1802
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: COPY statement abort

Post by JimKnicely » Thu Jun 26, 2014 2:24 pm

bhaggyit,
Could you please advise the way around to cause the COPY statement to reject the record instead of aborting it.
Maybe you can try something like this?

Code: Select all

F:\temp5>vsql -c "\d test"
                                   List of Fields by Tables
  Schema  | Table | Column |   Type    | Size | Default | Not Null | Primary Key | Foreign Key
----------+-------+--------+-----------+------+---------+----------+-------------+-------------
 snowfall | test  | col1   | timestamp |    8 |         | f        | f           |
(1 row)

F:\temp5>more t.txt
20140625:d0
20140625:1130

F:\temp5>vsql -c "COPY test (TIME FILLER VARCHAR(10240), col1 AS TO_TIMESTAMP(TIME,'MMDDYYYY:HH24MI')) FROM LOCAL 'F:\temp5\t.txt';"
ERROR 2005:  Invalid input for HH24: "d0"

F:\temp5>vsql -c "COPY test (TIME FILLER VARCHAR(10240), col1 AS DECODE(REGEXP_LIKE(REPLACE(TIME, ':', ''), '^[0-9.-]+$' ), 1, TO_TIMESTAMP(TIME,'MMDDYYYY:HH24MI'), NULL)) FROM LOCAL 'F:\temp5\t.txt';"
 Rows Loaded
-------------
           2
(1 row)

F:\temp5>vsql -c "DELETE FROM test WHERE col1 IS NULL;COMMIT;"
COMMIT

F:\temp5>vsql -c "SELECT * FROM test;"
        col1
---------------------
 0626-08-16 11:30:00
(1 row)
Note: As sKwa pointed out, the format of your date may not be correct.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Database Development”