> I'm trying to "convert" the sample db found in "The Practical SQL 
> Handbook" Bowman, et al. to postgres.  When trying to create the  
> following table I get "parser: parse error at or near 'null'" 
> create table authors (
>         zip             char(5)         null
> );
> The table creation works fine twith the NOT NULL but won't work with  
> the NULL. NULL is supported isn't it?

The NULL constraint syntax is *not* supported, since it results in
shift/reduce conflicts in our yacc parser. This is because the token is
ambiguous with other uses of NULL in the same area, at least as far as
yacc is concerned.

However, the default behavior for all columns is to allow NULL values,
so it is a noise word which can be omitted without ill effect.

                      - Tom

Wed, 23 Dec 1998 15:38:14 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: NULL & NOT NULL 
> > > I'm trying to "convert" the sample db found in "The Practical SQL
> > > Handbook" Bowman, et al. to postgres.  When trying to create the
> > > following table I get "parser: parse error at or near 'null'"
> > > create table authors (
> > >         zip             char(5)         null
> > > );
> > > The table creation works fine with the NOT NULL but won't work 
> > > with the NULL. NULL is supported isn't it?
> > The NULL constraint syntax is *not* supported, since it results in
> > shift/reduce conflicts in our yacc parser. This is because the token 
> > is ambiguous with other uses of NULL in the same area, at least as 
> > far as yacc is concerned.

Sheesh. After that long song and dance about why we can't implement
this, it turns out that it works fine. We had been trying to implement a
slightly different syntax, "WITH NULL", which conflicted with the
SQL92-defined data type declaration "TIMESTAMP WITH TIME ZONE".

The "Practical SQL Handbook"-compatible form will be available in the
next full release of Postgres. Thanks.

                      - Tom

Thu, 24 Dec 1998 14:05:59 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: NULL & NOT NULL
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
>>>>>> create table authors (
>>>>>> zip             char(5)         null
>>>>>> );

> Sheesh. After that long song and dance about why we can't implement
> this, it turns out that it works fine. We had been trying to implement a
> slightly different syntax, "WITH NULL", which conflicted with the
> SQL92-defined data type declaration "TIMESTAMP WITH TIME ZONE".

> The "Practical SQL Handbook"-compatible form will be available in the
> next full release of Postgres. Thanks.

Now that we have the syntax problem straightened out: I'm still confused
about the semantics.  Does a "NULL" constraint say that the field
*must* be null, or only that it *can* be null (in which case NULL is
just a noise word, since that's the default condition)?  I had assumed
the former, but Bruce seemed to think the latter...

			regards, tom lane

Thu, 24 Dec 1998 09:33:18 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: NULL & NOT NULL 
> "Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> >>>>>> create table authors (
> >>>>>> zip             char(5)         null
> >>>>>> );
> 
> > Sheesh. After that long song and dance about why we can't implement
> > this, it turns out that it works fine. We had been trying to implement a
> > slightly different syntax, "WITH NULL", which conflicted with the
> > SQL92-defined data type declaration "TIMESTAMP WITH TIME ZONE".
> 
> > The "Practical SQL Handbook"-compatible form will be available in the
> > next full release of Postgres. Thanks.
> 
> Now that we have the syntax problem straightened out: I'm still confused
> about the semantics.  Does a "NULL" constraint say that the field
> *must* be null, or only that it *can* be null (in which case NULL is
> just a noise word, since that's the default condition)?  I had assumed
> the former, but Bruce seemed to think the latter...


Can be null.  Noise word.  At least that is what I rememeber Thomas
saying, and because it was noise, we removed it.  In fact, it doesn't
look like the standard accepts it, but there is no reason we can't.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 24 Dec 1998 10:47:27 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: NULL & NOT NULL
> > > The "Practical SQL Handbook"-compatible form will be available in 
> > > the next full release of Postgres. Thanks.
> > Now that we have the syntax problem straightened out: I'm still 
> > confused about the semantics.
> Can be null.  Noise word.  At least that is what I rememeber Thomas
> saying, and because it was noise, we removed it.  In fact, it doesn't
> look like the standard accepts it, but there is no reason we can't.

Yeah, it's noise. And not in SQL92. I've flagged places in gram.y which
are syntax extensions included just to be compatible with specific
products so that if we ever run into parser conflicts with them we can
yank them out. The M$ usage for "where var = NULL" rather than the SQL
standard usage "where var is null" is another example of this.

I don't own "The Practical SQL Handbook", but it is funny that it
contains examples which are not part of the SQL standard (this is the
second one as I recall; can't remember the first one though).

                         - Tom

Mon, 28 Dec 1998 15:50:57 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: NULL & NOT NULL

On 28-Dec-98 Thomas G. Lockhart wrote:

> I don't own "The Practical SQL Handbook", but it is funny that it
> contains examples which are not part of the SQL standard (this is the
> second one as I recall; can't remember the first one though).

I've got the book if ya need something looked up or whatever.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
       # include <std/disclaimers.h>                   TEAM-OS2
   Online Searchable Campground Listings    http://www.camping-usa.com
       "There is no outfit less entitled to lecture me about bloat
               than the federal government"  -- Tony Snow
==========================================================================



Mon, 28 Dec 1998 11:19:15 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] Re: NULL & NOT NULL
Bruce Momjian wrote:
> 
> > "Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> > >>>>>> create table authors (
> > >>>>>> zip             char(5)         null
> > >>>>>> );
> >
> > > Sheesh. After that long song and dance about why we can't implement
> > > this, it turns out that it works fine. We had been trying to implement a
> > > slightly different syntax, "WITH NULL", which conflicted with the
> > > SQL92-defined data type declaration "TIMESTAMP WITH TIME ZONE".
> >
> > > The "Practical SQL Handbook"-compatible form will be available in the
> > > next full release of Postgres. Thanks.
> >
> > Now that we have the syntax problem straightened out: I'm still confused
> > about the semantics.  Does a "NULL" constraint say that the field
> > *must* be null, or only that it *can* be null (in which case NULL is
> > just a noise word, since that's the default condition)?  I had assumed
> > the former, but Bruce seemed to think the latter...
> 
> Can be null.  Noise word.  At least that is what I rememeber Thomas
> saying, and because it was noise, we removed it.  In fact, it doesn't
> look like the standard accepts it, but there is no reason we can't.


This NULL clause is not part of constraints it is a default option and
we already support it,
there's nothing like: 
CREATE TABLE table1 (field1 type NULL) in SQL92.

but the following is SQL92 and it works on PostgreSQL:
 
prova=> CREATE TABLE table1 (field1 INTEGER DEFAULT NULL);
CREATE


SQL92 syntax:

         11.5  <default clause>

         Function

         Specify the default for a column or domain.

         Format

         <default clause> ::=
                DEFAULT <default option>

         <default option> ::=
                <literal>
              | <datetime value function>
              | USER
              | CURRENT_USER
              | SESSION_USER
              | SYSTEM_USER
              | NULL

-Jose'-

Tue, 29 Dec 1998 15:43:13 +0100
"Jose' Soares" <jose@sferacarta.com>
Re: [HACKERS] Re: NULL & NOT NULL
> This NULL clause is not part of constraints it is a default option and
> we already support it,
> prova=> CREATE TABLE table1 (field1 INTEGER DEFAULT NULL);
> CREATE

That is certainly a clearer way of specifying it. Should we forget about
the other syntax?

                       - Tom

Tue, 29 Dec 1998 15:44:29 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: NULL & NOT NULL
Thomas G. Lockhart wrote:
> 
> > This NULL clause is not part of constraints it is a default option and
> > we already support it,
> > prova=> CREATE TABLE table1 (field1 INTEGER DEFAULT NULL);
> > CREATE
> 
> That is certainly a clearer way of specifying it. Should we forget about
> the other syntax?
> 
>                        - Tom

Imho yes. The syntax CREATE TABLE table (field type NULL) has no sense
the NULL keyword may be used on a DEFAULT clause (if you want to specify
a default value)
or on a column constraint (if you want to avoid data integrity
violation).

1) Column Constraint definition:
            [ CONSTRAINT name ] NOT NULL
   
2) Default clause:
            DEFAULT NULL

-Jose'-

Wed, 30 Dec 1998 16:14:29 +0100
"Jose' Soares" <jose@sferacarta.com>
Re: [HACKERS] Re: NULL & NOT NULL