Re: ERROR: Bad timestamp external representation ''

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>