Thread: ERROR: Bad timestamp external representation ''

Hello

inserting a datetime values is possible with '2001-03-03' for example,
but inserting a 'NULL' is not possible and inserting a NULL is
possible.

Example:

PostgreSQL:

insert into table test (...) values ('2001-03-03') works.

insert into table test (...) values ('NULL') does not work,
but in mysql it works.

It would be great if there would be a way for 'NULL' and NULL to make
everything more compatible.

inserting a "NULL" in MySQL will be displayed as "NULL" and in
postgres as "" but if you select the row with NULL it is both
dispplayed correctly (select .. from .. where date = 'NULL' works, if
content = "" or "NULL", thats good).


--
Boris [MCSE, CNA]
...................................................................
 X-ITEC : Consulting * Programming * Net-Security * Crypto-Research
........: [PRIVATE ADDRESS:]
        : Boris Köster eMail koester@x-itec.de http://www.x-itec.de
        : Grüne 33-57368 Lennestadt Germany Tel: +49 (0)2721 989400
        : 101  PERFECTION - SECURITY - STABILITY - FUNCTIONALITY
........:..........................................................

Everything I am writing is (c) by Boris Köster and may not be
rewritten or distributed in any way without my permission.

Boris writes:

> insert into table test (...) values ('2001-03-03') works.
>
> insert into table test (...) values ('NULL') does not work,
> but in mysql it works.

That doesn't necessarily mean that MySQL is right.

> It would be great if there would be a way for 'NULL' and NULL to make
> everything more compatible.

That would certainly make everything *less* compatible.

> inserting a "NULL" in MySQL will be displayed as "NULL" and in
> postgres as "" but if you select the row with NULL it is both
> dispplayed correctly (select .. from .. where date = 'NULL' works, if
> content = "" or "NULL", thats good).

If you want to change the display of NULLs in the psql frontend, use the
\pset null command.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

Boris <koester@x-itec.de> writes:
> insert into table test (...) values ('NULL') does not work,
> but in mysql it works.

MySQL does not define the SQL standard.

Write
    insert into table test (...) values (NULL);
if you want something that is portable and standards-compliant.

            regards, tom lane

On Sun, Mar 18, 2001 at 08:16:01PM +0100, Boris wrote:
> Hello
>
> inserting a datetime values is possible with '2001-03-03' for example,
> but inserting a 'NULL' is not possible and inserting a NULL is
> possible.
>
> Example:
>
> PostgreSQL:
>
> insert into table test (...) values ('2001-03-03') works.
>
> insert into table test (...) values ('NULL') does not work,
> but in mysql it works.

Try an unquoted NULL.

> It would be great if there would be a way for 'NULL' and NULL to make
> everything more compatible.

But "NULL" or 'NULL' is a quoted string.  What does MySQL do if you
try to insert "NULL" into a string field.  Do you get the phrase "NULL"
or do you get a field set to NULL?  Similarly, if the character field
contains the word "NULL", what do you get with a 'select foo from
bar where astring = "NULL"'?  Is it telling you the string IS NULL or
the value of the string == "NULL"?

> inserting a "NULL" in MySQL will be displayed as "NULL" and in
> postgres as "" but if you select the row with NULL it is both
> dispplayed correctly (select .. from .. where date = 'NULL' works, if
> content = "" or "NULL", thats good).

Well, nothing is ever supposed to equal NULL.  NULL is indeterminate,
unknown, can't be compared to...  Just because MySQL does it wrong,
doesn't mean that PostgreSQL should. The only "proper" way to test
for the NULL condition is to use IS NULL (or IS NOT NULL, etc...).
So queries should be "select foo from bar where adate is null", or
similar...

--
Eric G. Miller <egm2@jps.net>

Hello Tom,

Sunday, March 18, 2001, 8:30:11 PM, you wrote:



TL> Write
TL>         insert into table test (...) values (NULL);
TL> if you want something that is portable and standards-compliant.

TL>                         regards, tom lane

Good to know, thanks.

Thanks for the other people for such a fast reply, too!


--
Boris

Hello Eric,

Sunday, March 18, 2001, 9:14:19 PM, you wrote:



EGM> But "NULL" or 'NULL' is a quoted string.  What does MySQL do if you
EGM> try to insert "NULL" into a string field.  Do you get the phrase "NULL"

Yes this is correct, but it does not fit on a datetime field.

It is very useful if you are programming something with php and you
can use '$variable' and if you not need to check if the variable is
empty or not.

Now I have to make some changes in my code, but its ok no problem. I
like postgres more than mysql -))


EGM> Well, nothing is ever supposed to equal NULL.  NULL is indeterminate,
EGM> unknown, can't be compared to...  Just because MySQL does it wrong,
EGM> doesn't mean that PostgreSQL should. The only "proper" way to test
EGM> for the NULL condition is to use IS NULL (or IS NOT NULL, etc...).
EGM> So queries should be "select foo from bar where adate is null", or
EGM> similar...

Ok, thats a great idea! Thanks!



--
Boris