Re: insert into: NULL in date column

On 1/11/19 4:00 PM, Rich Shepard wrote:
> On Fri, 11 Jan 2019, Ken Tanzer wrote:
>
>> I think the problem is actually that you're trying to represent your NULL
>> dates with '', which PG doesn't like.
>
> Ken,
>
>   That's certainly how I saw the error message.
>
>> 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.
>
>   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.

Dates are not treated as strings they are treated as dates. There is
built casting for strings that are valid dates though:

create table date_test(dt_fld date);

insert into date_test values('01/11/19');
INSERT 0 1

--Trying MySQL 'null':)
insert into date_test values('00/00/0000');

ERROR: date/time field value out of range: "00/00/0000"

LINE 1: insert into date_test values('00/00/0000');

insert into date_test values('');

ERROR: invalid input syntax for type date: ""

LINE 1: insert into date_test values('');

As pointed out you are being told '' is not a valid date.

>
> Thanks,
>
> Rich
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com