Home > mailing lists

Re: insert into: NULL in date column - Mailing list pgsql-general

From	Ken Tanzer
Subject	Re: insert into: NULL in date column
Date	January 12, 2019 02:34:35
Msg-id	CAD3a31Vh9y8QirWXU5TPD_XgaquMqXYfnK1PMOCOntyESp=yGw@mail.gmail.com Whole thread Raw
In response to	Re: insert into: NULL in date column (Rob Sargent <robjsargent@gmail.com>)
Responses	Re: insert into: NULL in date column (Rich Shepard <rshepard@appl-ecosys.com>)
List	pgsql-general

Tree view

On Fri, Jan 11, 2019 at 3:25 PM Rob Sargent <robjsargent@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