insert into: NULL in date column

classic Classic list List threaded Threaded
23 messages Options
12
Reply | Threaded
Open this post in threaded view
|

insert into: NULL in date column

Rich Shepard
A table has this column definition:

next_contact date DEFAULT '2020-11-06'
                CONSTRAINT valid_next_date
                CHECK (next_contact >= CURRENT_DATE),

(and I don't know that it needs a default).

In an input statement that column is left empty ('') when there's no defined
date. When I try to input that table I get a format error:

psql:activities.sql:2: ERROR:  invalid input syntax for type date: ""
LINE 2: ...reaction they''ve experienced environmental issues.','','');
                                                                 ^
and my web research has not shown me my error. Removing the check constraint
does not eliminate that error.

Please show me what I've done incorrectly.

TIA,

Rich

lup
Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

lup

On 1/11/19 3:56 PM, Rich Shepard wrote:

> A table has this column definition:
>
> next_contact date DEFAULT '2020-11-06'
>                CONSTRAINT valid_next_date
>                CHECK (next_contact >= CURRENT_DATE),
>
> (and I don't know that it needs a default).
>
> In an input statement that column is left empty ('') when there's no
> defined
> date. When I try to input that table I get a format error:
>
> psql:activities.sql:2: ERROR:  invalid input syntax for type date: ""
> LINE 2: ...reaction they''ve experienced environmental issues.','','');
>                                                                 ^
> and my web research has not shown me my error. Removing the check
> constraint
> does not eliminate that error.
>
> Please show me what I've done incorrectly.
>
> TIA,
>
> Rich
>

Miss-matched single quotes in activities.sql?


Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

Rich Shepard
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

lup
Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

lup

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.

Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

Ken Tanzer
On Fri, Jan 11, 2019 at 3:25 PM Rob Sargent <[hidden email]> 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
(253) 245-3801

learn more about AGENCY or
follow the discussion.
Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

Rich Shepard
In reply to this post by lup
On Fri, 11 Jan 2019, Rob Sargent wrote:

> Something syntactically askew I think.

Rob,

   I agree that's the problem. Why there's a problem is what I need to learn.

Thanks,

Rich



Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

Rich Shepard
In reply to this post by Ken Tanzer
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.

Thanks,

Rich

Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

David G Johnston
On Fri, Jan 11, 2019 at 5:01 PM Rich Shepard <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

David G Johnston
In reply to this post by Rich Shepard
On Fri, Jan 11, 2019 at 3:56 PM Rich Shepard <[hidden email]> wrote:
>
> A table has this column definition:
>
> next_contact date DEFAULT '2020-11-06'
>                 CONSTRAINT valid_next_date
>                 CHECK (next_contact >= CURRENT_DATE),
>
> (and I don't know that it needs a default).

The default does seem a bit arbitrary and pointless...

David J.

Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

David G Johnston
In reply to this post by lup
On Fri, Jan 11, 2019 at 4:25 PM Rob Sargent <[hidden email]> wrote:
> 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.

If the problem was where you described the parser would never have
gotten to the point of trying to pass an empty string to a date
constructor resulting in a runtime error.  It would have failed at
compile time with a very different error probably relating to
"malformed statement" or "unexpected identifier".

The OP provided sufficient detail (though an actual complete failing
command would have been nice) to pinpoint the misunderstanding that
the empty string and null are not the same thing at that converting
the empty string to a date is not possible (i.e., it doesn't just
silently return null for invalid input, one must pass in null
explicitly if one wishes to construct a date typed null.)

David J.

Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

Adrian Klaver-4
In reply to this post by Rich Shepard
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

Ricardo Martin Gomez
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

De: Adrian Klaver
Enviado: viernes, 11 de enero 22:09
Asunto: Re: insert into: NULL in date column
Para: Rich Shepard, [hidden email]


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 [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

Rich Shepard
In reply to this post by David G Johnston
On Fri, 11 Jan 2019, David G. Johnston wrote:

> VALUES (1, null, 3) is valid, VALUES (1,,3) is not.

David,

   Using null occurred to me when I saw that an empty space still failed.
Thanks for clarifying and confirming.

Best regards,

Rich

Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

Rich Shepard
In reply to this post by David G Johnston
On Fri, 11 Jan 2019, David G. Johnston wrote:

> The default does seem a bit arbitrary and pointless...

David,

   That answers my question about it. Thanks again.

Best regards,

Rich

Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

Rich Shepard
In reply to this post by Ricardo Martin Gomez
On Sat, 12 Jan 2019, Ricardo Martin Gomez wrote:

> 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.

Ricardo,

   I thought of using an explicit null and David confirmed that to be the
solution. Also, he answered my question that having a default and check
constraint are not needed.

Thanks,

Rich

Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

David G Johnston
On Sat, Jan 12, 2019 at 6:43 AM Rich Shepard <[hidden email]> wrote:

>
> On Sat, 12 Jan 2019, Ricardo Martin Gomez wrote:
>
> > 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.
>
> Ricardo,
>
>    I thought of using an explicit null and David confirmed that to be the
> solution. Also, he answered my question that having a default and check
> constraint are not needed.

Actually, you didn't ask about the check constraint, which is actually
horribly broken since current_date is not an immutable function.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

Rich Shepard
On Sat, 12 Jan 2019, David G. Johnston wrote:

> Actually, you didn't ask about the check constraint, which is actually
> horribly broken since current_date is not an immutable function.

David,

   I know that nulls cannot be validly used in comparisons which makes the
check constraint FUBAR.

Thanks,

Rich

Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

David G Johnston
On Sat, Jan 12, 2019 at 9:01 AM Rich Shepard <[hidden email]> wrote:
>
> On Sat, 12 Jan 2019, David G. Johnston wrote:
>
> > Actually, you didn't ask about the check constraint, which is actually
> > horribly broken since current_date is not an immutable function.
>>
>    I know that nulls cannot be validly used in comparisons which makes the
> check constraint FUBAR.

NULL isn't the problem - a check constraint can resolve to unknown in
which case it behaves the same as if it resolved as true (i.e., its
basically a <check> IS NOT FALSE test in the backend).  This is
actually a nice feature of check constraints since for nullable
columns you don't have to write "col IS NULL OR <the check I really
care about>"

The problem is that check constraints are only applied at time of data
change.  If you insert a record whose date is 3 days from now the
check constraint passes today and (in theory) for the next couple of
days.  After which the constraint fails - but you are INFORMED ONLY IF
THE RECORD IS INSERTED AGAIN.  So basically you will not see a problem
until you attempt to restore your data on some future date and much of
your data fails to restore because those dates are no longer in the
future.

If you want to check for a future date you should probably also store
the date you are comparing against and have the check constraint
reference both fields.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

Rich Shepard
On Sat, 12 Jan 2019, David G. Johnston wrote:

> NULL isn't the problem - a check constraint can resolve to unknown in
> which case it behaves the same as if it resolved as true (i.e., its
> basically a <check> IS NOT FALSE test in the backend). This is actually a
> nice feature of check constraints since for nullable columns you don't
> have to write "col IS NULL OR <the check I really care about>"

David,

   Thanks for correcting me.

> The problem is that check constraints are only applied at time of data
> change. If you insert a record whose date is 3 days from now the check
> constraint passes today and (in theory) for the next couple of days. After
> which the constraint fails - but you are INFORMED ONLY IF THE RECORD IS
> INSERTED AGAIN. So basically you will not see a problem until you attempt
> to restore your data on some future date and much of your data fails to
> restore because those dates are no longer in the future.

   I thought that the check constraint applied at data entry, too. If not,
then I'll have either wxPython or SQLAlchemy ensure that the next_contact
date is later than the contact date.

> If you want to check for a future date you should probably also store the
> date you are comparing against and have the check constraint reference
> both fields.

   The contact date is always entered in a new row, but the next_contact date
might not be if there's nothing scheduled.

Best regards,

Rich


Reply | Threaded
Open this post in threaded view
|

Re: insert into: NULL in date column

Ricardo Martin Gomez
Hi,
In MYSQL Null date is equal '01/01/1900' or '01/01/1970', I don't remember but you can also use the same logic for the check_constraint.
Regards


From: Rich Shepard <[hidden email]>
Sent: Saturday, January 12, 2019 1:54:47 PM
To: [hidden email]
Subject: Re: insert into: NULL in date column
 
On Sat, 12 Jan 2019, David G. Johnston wrote:

> NULL isn't the problem - a check constraint can resolve to unknown in
> which case it behaves the same as if it resolved as true (i.e., its
> basically a <check> IS NOT FALSE test in the backend). This is actually a
> nice feature of check constraints since for nullable columns you don't
> have to write "col IS NULL OR <the check I really care about>"

David,

   Thanks for correcting me.

> The problem is that check constraints are only applied at time of data
> change. If you insert a record whose date is 3 days from now the check
> constraint passes today and (in theory) for the next couple of days. After
> which the constraint fails - but you are INFORMED ONLY IF THE RECORD IS
> INSERTED AGAIN. So basically you will not see a problem until you attempt
> to restore your data on some future date and much of your data fails to
> restore because those dates are no longer in the future.

   I thought that the check constraint applied at data entry, too. If not,
then I'll have either wxPython or SQLAlchemy ensure that the next_contact
date is later than the contact date.

> If you want to check for a future date you should probably also store the
> date you are comparing against and have the check constraint reference
> both fields.

   The contact date is always entered in a new row, but the next_contact date
might not be if there's nothing scheduled.

Best regards,

Rich


12