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