From: | Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> |
---|---|
To: | Rob Sargent <robjsargent(at)gmail(dot)com> |
Cc: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: insert into: NULL in date column |
Date: | 2019-01-11 23:34:35 |
Message-ID: | CAD3a31Vh9y8QirWXU5TPD_XgaquMqXYfnK1PMOCOntyESp=yGw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Jan 11, 2019 at 3:25 PM Rob Sargent <robjsargent(at)gmail(dot)com> wrote:
>
> On 1/11/19 4:21 PM, Rich Shepard wrote:
> > On Fri, 11 Jan 2019, Rob Sargent wrote:
> >
> >>> psql:activities.sql:2: ERROR: invalid input syntax for type date: ""
> >>> LINE 2: ...reaction they''ve experienced environmental issues.','','');
> > ^
> >> Miss-matched single quotes in activities.sql?
> >
> > Rob,
> >
> > I don't see the mis-match. The preceeding text column escapes the
> > internal
> > single quotes by doubling them while the entire string is single quoted.
> >
> > Regards,
> >
> > Rich
> >
> We don't have more context in "activities.sql" but if your OP was
> verbatim, it's keeling over on the comma ending the long text string.
> Something syntactically askew I think.
>
> I think the problem is actually that you're trying to represent your NULL
dates with '', which PG doesn't like.
cat test.csv
my_text,my_date,my_int
'Some Text','1/1/18',3
'More Text,,2
'Enough','',1
CREATE TEMP TABLE my_test (my_text TEXT, my_date DATE, my_int INT);
\copy my_test FROM test.csv WITH CSV HEADER
ERROR: invalid input syntax for type date: "''"
CONTEXT: COPY my_test, line 4, column my_date: "''"
You'll note that it breaks on the last line, which is like yours, not
the one before it.
I think there may be some other ways to specify the NULL value, but
I'm not really sure about that part.
Cheers,
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Shepard | 2019-01-11 23:57:40 | Re: insert into: NULL in date column |
Previous Message | Rob Sargent | 2019-01-11 23:24:56 | Re: insert into: NULL in date column |