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
COPY statement abort
Moderator: NorbertKrupa
Re: COPY statement abort
Hi!
[DELETED]
[DELETED]
Last edited by id10t on Wed May 06, 2015 5:10 pm, edited 1 time in total.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: COPY statement abort
bhaggyit,
Note: As sKwa pointed out, the format of your date may not be correct.
Maybe you can try something like this?Could you please advise the way around to cause the COPY statement to reject the record instead of aborting it.
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.