Loading Negative (BC) Dates: Quick Tip

Jim Knicely authored this tip. I was asked recently how to load a negative date (one representing a BC date) into Vertica. Although negative dates are not valid in Vertica, the FILLER parameter of the COPY command can be used to load them. Example: dbadmin=> \d public.test List of Fields by Tables Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key --------+-------+--------+------+------+---------+----------+-------------+------------- public | test | c1 | date | 8 | | f | f | (1 row) dbadmin=> \! cat /home/dbadmin/date.txt -4712-01-01 00:00:00 dbadmin=> COPY public.test FROM ‘/home/dbadmin/date.txt’ REJECTED DATA TABLE public.test_bad; Rows Loaded ————- 0 (1 row) dbadmin=> SELECT rejected_data, rejected_reason FROM test_bad; rejected_data | rejected_reason ———————-+————————————————————————————————————————- -4712-01-01 00:00:00 | Invalid date format ‘-4712-01-01 00:00:00’ for column 1 (c1).Timezone displacement out of range: “-4712-01-01 00:00:00” (1 row) dbadmin=> COPY public.test (c1_filler FILLER VARCHAR, c1 AS to_date(c1_filler, ‘YYYY-MM-DD HR:MM:SS’)) FROM ‘/home/dbadmin/date.txt’ REJECTED DATA TABLE public.test_bad; Rows Loaded ————- 1 (1 row) dbadmin=> SELECT * FROM public.test; c1 ————— 4713-01-01 BC (1 row) Have Fun!