Thread: NULL & NOT NULL

I'm  (1) new to postgres & (2) 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 (
    au_id        char(11)    not null,
    au_lname    varchar(40)    not null,
    .
    .
    .
    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?  I can't see where I'm making a syntax
error.  Got me stumped.  TIA.

........Bill Sneed Prospect, Maine......

> 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

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