Re: insert into: NULL in date column

Hi, one question.
Do you put explicit "NULL" in the column value?
Other option is.
You have to delete the column "next_contact" in your INSERT clause.
So, if the column has a default value, this value Will be inserted. Else Null value will be inserted.

Regards

Obtener Outlook para Android<https://aka.ms/ghei36>

De: Adrian Klaver
Enviado: viernes, 11 de enero 22:09
Asunto: Re: insert into: NULL in date column
Para: Rich Shepard, pgsql-general(at)lists(dot)postgresql(dot)org

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