Re: insert into: NULL in date column

[Rich Shepard]

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

Re: insert into: NULL in date column

[David G. Johnston]

On Sat, Jan 12, 2019 at 9:54 AM Rich Shepard  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.

Re: insert into: NULL in date column

[David G. Johnston]

On Sat, Jan 12, 2019 at 10:08 AM Ricardo Martin Gomez
 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 the check_constraint.
> Regards

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

David J.

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

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


From: Rich Shepard 
Sent: Saturday, January 12, 2019 1:54:47 PM
To: pgsql-general@lists.postgresql.org
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  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 "

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

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


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

Re: insert into: NULL in date column

[David G. Johnston]

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

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

Re: insert into: NULL in date column

[David G. Johnston]

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

Re: insert into: NULL in date column

[Rich Shepard]

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

Re: insert into: NULL in date column

[Rich Shepard]

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

Re: insert into: NULL in date column

[Rich Shepard]

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

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

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@lists.postgresql.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/'); ERROR: date/time field value out of range: "00/00/" 
LINE 1: insert into date_test values('00/00/'); 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.kla...@aklaver.com

Re: insert into: NULL in date column

[Adrian Klaver]

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/'); 



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



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

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

Re: insert into: NULL in date column

[David G. Johnston]

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

Re: insert into: NULL in date column

[David G. Johnston]

On Fri, Jan 11, 2019 at 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).

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

David J.

Re: insert into: NULL in date column

[David G. Johnston]

On Fri, Jan 11, 2019 at 5:01 PM Rich Shepard  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.

Re: insert into: NULL in date column

[Rich Shepard]

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

Re: insert into: NULL in date column

[Rich Shepard]

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

Re: insert into: NULL in date column

[Ken Tanzer]

On Fri, Jan 11, 2019 at 3:25 PM Rob Sargent  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
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: insert into: NULL in date column

[Rob Sargent]

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.

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

Re: insert into: NULL in date column

[Rob Sargent]

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?

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