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, Pennsylvania 19026

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 entitled to 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'-