From: | Geoff Winkless <pgsqladmin(at)geoff(dot)dj> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | implicit CAST on CSV COPY FROM |
Date: | 2015-04-02 11:07:35 |
Message-ID: | CAEzk6ffuazsK2ifG-aGYpMKSoeBnyEMQM8zLm6WwfGfsTPi14Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ravi Kiran | 2015-04-02 13:52:07 | Relation name stored in Postgres |
Previous Message | Pavel Stehule | 2015-04-02 08:05:56 | Re: Why doesn't `RAISE EXCEPTION` provide error context? |