Re: [SQL] NULL - Mailing list pgsql-sql

"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:
> My recollection of this thread was that no one found any reason to
> dis-allow NULL as a column (pseudo)constraint, other than it not being
> in the SQL92 standard. In particular, I understood Jan to say that he
> tried it, and received no shift/reduce conflicts from flex. I think we
> all agree with Tom that there's no need for it, so we shouldn't give up
> anything else to get it, but several people mentioned having it easied
> porting from Sybase, or some other commercial db, where the machine
> generated DDL dumps include the NULL constraints. So, class it with the
> other compatability hacks, and include it, perhaps?

My concern about this is not so much whether it would work at the moment
as whether it will be a nasty limitation in the future.  Once we start
allowing NULL as a column constraint we will get justifiable complaints
if we take it out again.  But because of SQL's rather silly decision
not to put any punctuation between column constraint clauses, anything
that can start a column constraint clause can't also be a valid entry
in an expression.  Consider
CREATE TABLE foo (field1 int DEFAULT 2 ++ NULL)

Here, the grammar cannot figure out whether the default expression is
"2 ++ NULL" (with ++ an infix operator) or "2 ++" (++ a postfix operator)
and NULL a separate constraint clause.

We currently work around this for NOT NULL column constraints by making
default expressions be b_expr's, which don't include the boolean
operators --- if you need a boolean operator in a default expression,
you have to put parentheses around the whole thing.  I don't want to
have to restrict b_expr further in order to support NULL column
constraints.

I think the only reason Jan's test worked is that NULL as an expression
value is handled in a rather hacky way --- there's a separate
a_expr_or_null nonterminal --- and I've been intending to try to
eliminate that kluge.  Right now, things like this draw parse errors:
select null::text;ERROR:  parser: parse error at or near "::"select (cast null as text);ERROR:  parser: parse error at
ornear "null"
 

because NULL isn't parsed as a fully valid expression value.  I'd
rather try to clean that up, because it is (a) useful and (b) legal SQL,
rather than make the world safe for a column constraint phrase that
is (a) useless and (b) not legal SQL.
        regards, tom lane