Обсуждение: implicit CAST on CSV COPY FROM
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
On 04/02/2015 04:07 AM, Geoff Winkless wrote: > 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. FYI Postgres will handle 20150402 with out any changes: test=> select '20150402'::date; date ------------ 2015-04-02 Postgres is applying the valid date test before doing any casting and 0 is not a valid date: test=> select '0'::date; ERROR: date/time field value out of range: "0" LINE 1: select '0'::date; ^ HINT: Perhaps you need a different "datestyle" setting. Your options: 1) Use a combination of NULL, FORCE_NOT_NULL, FORCE_NULL to get the 0 to be null, though I could see that getting messy. 2) Pre-process the data to turn 0 into NULL. 3) With Postgres 9.3+ run the COPY FROM through a PROGRAM to do 1 http://www.postgresql.org/docs/9.4/interactive/sql-copy.html 4) COPY FROM to a holding table that has a varchar field for the date data and then do a conversion and transfer to the final table. > > Thanks! > > Geoff -- Adrian Klaver adrian.klaver@aklaver.com