On Fri, Jan 11, 2019 at 5:01 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
> On Fri, 11 Jan 2019, Ken Tanzer wrote:
> > \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: "''"
Right problem wrong solution since it appears that the OP is using
INSERT/VALUES instead of COPY and you cannot just leave an empty field
in a VALUES expression.
> > You'll note that it breaks on the last line, which is like yours, not
> > the one before it.
>
> Huh! I'll leave off the quote marks and see if that makes a difference ...
> tomorrow morning. Since dates are treated as strings I thought their absence
> also needed the quotes. Stay tuned to this mail list for test results.
Using whatever syntax your chosen method requires, you need to express
the fact that you wish to pass "null" into the input function for
date. The empty string is not "null". For copy you can simply using
nothing or, as the COPY command says is required in text mode, \N.
For VALUES you need to provide an actual expression that resolves to
null - the null literal is usually the expression of choice.
VALUES (1, null, 3) is valid, VALUES (1,,3) is not.
David J.