Hi I have a set of CSV data that I'm importing containing dates stored as INT values (eg 20150402). The value 0 represents a null date in this format.
I've created a function and cast that (ab)uses the system text::date cast: CREATE FUNCTION to_date(integer) RETURNS date AS $$SELECT CASE WHEN $1<=0 THEN NULL ELSE CONCAT(($1/10000)::text, '-', (($1/100)%100)::text, '-', ($1%100)::text)::date END$$ LANGUAGE SQL; CREATE CAST (integer AS date) WITH FUNCTION to_date(integer) AS IMPLICIT; This works fine if I CREATE TABLE mytable (dt date NULL); INSERT INTO mytable (dt) VALUES (0); mydb=# INSERT INTO mytable (dt) VALUES (0); INSERT 0 1 Time: 1.562 ms but if I use COPY mytable (dt) FROM STDIN WITH (FORMAT csv, HEADER false); 0 \. it fails. ERROR: date/time field value out of range: "0" HINT: Perhaps you need a different "datestyle" setting. CONTEXT: COPY mytable, line 1, column dt: "0" Is there a trick I can use to get COPY FROM to use my cast? Is it somehow treating all the CSV values as strings and then trying to cast to the target type? I tried creating similar CASTs for text, char and varchar to call my to_date function but none of them made a difference. Thanks! Geoff
