On Fri, Jan 11, 2019 at 3:25 PM Rob Sargent <robjsarg...@gmail.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.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.
