Обсуждение: 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
"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
> "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, Pennsylvania19026
> > > 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
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
entitledto lecture me about bloat than the federal government" -- Tony Snow
==========================================================================
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'-
> 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
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'-