The Vertica Database Forums

Posted: **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

Posted: **Wed Jun 25, 2014 5:15 pm**

Hi!

[DELETED]

Posted: **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.

The Vertica Database Forums

COPY statement abort

COPY statement abort

Re: COPY statement abort

Re: COPY statement abort