Thread: insert into: NULL in date column

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

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?

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

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.

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

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

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.

On Fri, Jan 11, 2019 at 3:56 PM Rich Shepard <rshepard@appl-ecosys.com> 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.

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

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.klaver@aklaver.com

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

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

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

On Sat, Jan 12, 2019 at 6:43 AM Rich Shepard <rshepard@appl-ecosys.com> 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.

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

On Sat, Jan 12, 2019 at 9:01 AM Rich Shepard <rshepard@appl-ecosys.com> 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.

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

On Sat, Jan 12, 2019 at 10:08 AM Ricardo Martin Gomez
<rimartingomez@hotmail.com> wrote:
>
> 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
thecheck_constraint.
 
> Regards

Why?  PostgreSQL doesn't need hacks around this...

David J.

On Sat, Jan 12, 2019 at 9:54 AM Rich Shepard <rshepard@appl-ecosys.com> wrote:
> > The problem is that check constraints are only applied at time of data
> > change.

>    I thought that the check constraint applied at data entry, too.

Inserting new data into a table qualifies as "data change" in my mind...

David J.

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

> Inserting new data into a table qualifies as "data change" in my mind...

David,

   Then it's certainly good enough for me. ;-)

   Looking at the text file which the application will replace there are
occasions when there's more than one contact on a single day. So there is no
value in having a date check constraint on this column.

Thanks again,

Rich