Thread: NULL

How about this according to SQL standard:

CREATE TABLE x { y integer NULL };

It suppose to mean that NULLs are explicitly allowed in 
this field.
Is this required by SQL-92?

TIA,
-- 
-----------------
Göran Thyni
On quiet nights you can hear Windows NT reboot!

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> 
> How about this according to SQL standard:
> 
> CREATE TABLE x { y integer NULL };
> 
> It suppose to mean that NULLs are explicitly allowed in 
> this field.
> Is this required by SQL-92?

If I remember correctly, only NOT NULL is supported by SQL-92.  Thomas
talked about adding support for it in some limited cases for 7.0.  There
are shift/reduce cases if it were allowed everywhere.


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

Goran Thyni <goran@kirra.net> writes:

> How about this according to SQL standard:
> 
> CREATE TABLE x { y integer NULL };
> 
> It suppose to mean that NULLs are explicitly allowed in 
> this field.
> Is this required by SQL-92?

No, it's not required.  This came up before with the examples from
"The Practical SQL Handbook".  It would be nice to allow it, but there
was some reason why to do so would be non-trivial, which I forget.
Anyway, it's not in SQL-92.

Bruce Momjian wrote:

> [Charset iso-8859-1 unsupported, filtering to ASCII...]
> >
> > How about this according to SQL standard:
> >
> > CREATE TABLE x { y integer NULL };
> >
> > It suppose to mean that NULLs are explicitly allowed in
> > this field.
> > Is this required by SQL-92?
>
> If I remember correctly, only NOT NULL is supported by SQL-92.  Thomas
> talked about adding support for it in some limited cases for 7.0.  There
> are shift/reduce cases if it were allowed everywhere.

Hmmm,

    I can't see any shift/reduce conflicts if I place a

        | NULL_P

    case into the ColConstraintElem: definition right between the

        | DEFAULT b_expr

    and

        | NOT NULL_P

    cases. Could it be that this reason is out of date?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

wieck@debis.com (Jan Wieck) writes:
>     I can't see any shift/reduce conflicts if I place a
>         | NULL_P
>     case into the ColConstraintElem: definition right between the
>         | DEFAULT b_expr
>     and
>         | NOT NULL_P
>     cases. Could it be that this reason is out of date?

Could be.  I remember tweaking the ColConstraint grammar to avoid
shift-reduce conflicts with NOT NULL, and it might be that that got
rid of the problem with NULL as well.

It's still not legal under the SQL92 spec, though, and I'd hate to see
us give up anything else in order to allow a content-free NULL spec to
be added...
        regards, tom lane

Bruce Stephens ha scritto:

> Goran Thyni <goran@kirra.net> writes:
>
> > How about this according to SQL standard:
> >
> > CREATE TABLE x { y integer NULL };
> >
> > It suppose to mean that NULLs are explicitly allowed in
> > this field.
> > Is this required by SQL-92?
>
> No, it's not required.  This came up before with the examples from
> "The Practical SQL Handbook".  It would be nice to allow it, but there
> was some reason why to do so would be non-trivial, which I forget.
> Anyway, it's not in SQL-92.
>
> ************

Sorry, I don't understand why we need this feature. This is completely out
of standard.

What's that mean ?

-  Is it a constraint to allow only NULL values ?  (unuseful)

- If this is a default value we already have this in: CREATE TABLE  table1 (field1  int DEFAULT NULL);

- According with SQL-92 every column can store a NULL value by default unless one specify a NOT NULL constraint for the
column.

José

> > > It suppose to mean that NULLs are explicitly allowed in
> > > this field.
> > > Is this required by SQL-92?
> >
> > No, it's not required.  This came up before with the examples from
> > "The Practical SQL Handbook".  It would be nice to allow it, but there
> > was some reason why to do so would be non-trivial, which I forget.
> > Anyway, it's not in SQL-92.
> >
> Sorry, I don't understand why we need this feature. This is completely ou=
> t
> of standard.
>
> What's that mean ?
>
> -  Is it a constraint to allow only NULL values ?  (unuseful)

    Useless? I NEED IT - URGENT - NOW - YESTERDAY.

    Then  I  could  create my tables with all required fields for
    the future, but prevent that  someone  stores  data  in  them
    until I drop the constraint.

    I vote for this :-)


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

Jan Wieck ha scritto:

> > > > It suppose to mean that NULLs are explicitly allowed in
> > > > this field.
> > > > Is this required by SQL-92?
> > >
> > > No, it's not required.  This came up before with the examples from
> > > "The Practical SQL Handbook".  It would be nice to allow it, but there
> > > was some reason why to do so would be non-trivial, which I forget.
> > > Anyway, it's not in SQL-92.
> > >
> > Sorry, I don't understand why we need this feature. This is completely ou=
> > t
> > of standard.
> >
> > What's that mean ?
> >
> > -  Is it a constraint to allow only NULL values ?  (unuseful)
>
>     Useless? I NEED IT - URGENT - NOW - YESTERDAY.
>
>     Then  I  could  create my tables with all required fields for
>     the future, but prevent that  someone  stores  data  in  them
>     until I drop the constraint.

Maybe I miss something here, Jan...I think you don't need such thing to do this
kind of work.

To drop the constraint you have:   1) download the table   2) modify the table structure (without constraint NULL)   3)
re-createthe table   4) reload it again.
 

You have the same effect as:   1) download the table   2) add new filelds to table structure   3) re-create the table
4)reload it again
 

...but, if want these fields from the begining you may create a CHECK contraint
for the field, like:

CREATE TABLE distributors (       did      DECIMAL(3),       name     VARCHAR(40),       avoid    INTEGER
CONSTRAINTcon1 CHECK (avoid is NULL)       );
 

insert into distributors values (33,'PIPPO',123);
ERROR:  ExecAppend: rejected due to CHECK constraint con1
insert into distributors values (33,'PIPPO',NULL);
INSERT 1484300 1
select * from distributors;
did|name |avoid
---+-----+-----33|PIPPO|
(1 row)

...and this is SQL-92 Standard   ;)

José

> Jan Wieck ha scritto:
>
> > > -  Is it a constraint to allow only NULL values ?  (unuseful)
> >
> >     Useless? I NEED IT - URGENT - NOW - YESTERDAY.
>
> Maybe I miss something here, Jan...

    Yepp - my smiley on the last line.

    It  wasn't  a  serious comment. More a joke and I really mean
    that such a definition is absolutely not  needed.  Maybe  I'm
    writing too much between the lines.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

jose soares <jose@sferacarta.com> writes:

> Bruce Stephens ha scritto:

> > No, it's not required.  This came up before with the examples from
> > "The Practical SQL Handbook".  It would be nice to allow it, but there
> > was some reason why to do so would be non-trivial, which I forget.
> > Anyway, it's not in SQL-92.

> - According with SQL-92 every column can store a NULL value by default
>   unless one specify a NOT NULL constraint for the column.

Yes.  NULL would just mean that NULLs are permitted.  So it's not
required, obviously (since this is the default).  

However, many books recommend that you should generally not allow
NULLs: thus, if you force yourself to explicitly say "NULL" or "NOT
NULL", that ought to be a prompt to consider the issue (and you can
spot cases which you may not have thought about by the absence of
either).  I imagine that's why "The Practical SQL Handbook" suggests
it.

> wieck@debis.com (Jan Wieck) writes:
> >     I can't see any shift/reduce conflicts if I place a
> >         | NULL_P
> >     case into the ColConstraintElem: definition right between the
> >         | DEFAULT b_expr
> >     and
> >         | NOT NULL_P
> >     cases. Could it be that this reason is out of date?
> 
> Could be.  I remember tweaking the ColConstraint grammar to avoid
> shift-reduce conflicts with NOT NULL, and it might be that that got
> rid of the problem with NULL as well.
> 
> It's still not legal under the SQL92 spec, though, and I'd hate to see
> us give up anything else in order to allow a content-free NULL spec to
> be added...

OK, I assume we are going to dis-allow NULL as a column constraint.  We
could allow it but emmit a notice.

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

On Mon, Nov 29, 1999 at 10:13:59PM -0500, Bruce Momjian wrote:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
> > wieck@debis.com (Jan Wieck) writes:
> > >     I can't see any shift/reduce conflicts if I place a
> > >         | NULL_P
> > >     case into the ColConstraintElem: definition right between the
> > >         | DEFAULT b_expr
> > >     and
> > >         | NOT NULL_P
> > >     cases. Could it be that this reason is out of date?
> > 
> > Could be.  I remember tweaking the ColConstraint grammar to avoid
> > shift-reduce conflicts with NOT NULL, and it might be that that got
> > rid of the problem with NULL as well.
> > 
> > It's still not legal under the SQL92 spec, though, and I'd hate to see
> > us give up anything else in order to allow a content-free NULL spec to
> > be added...
> 
> OK, I assume we are going to dis-allow NULL as a column constraint.  We
> could allow it but emmit a notice.

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?

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

On Tue, 30 Nov 1999, Ross J. Reedstrom wrote:

> 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

That's a pretty strong reason, in absence of any other good ones. The
product is called PostgreSQL and not PostgreSybase or whatever. We
shouldn't have to follow other vendors' questionable decisions. It's not
really that hard to prepare any incompatible dumps you might have.

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

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

On Tue, Nov 30, 1999 at 05:27:33PM +0100, Peter Eisentraut wrote:
> On Tue, 30 Nov 1999, Ross J. Reedstrom wrote:
> 
<snipped me saying NULL constraint not in SQL92 standard>

> 
> That's a pretty strong reason, in absence of any other good ones. The
> product is called PostgreSQL and not PostgreSybase or whatever. We
> shouldn't have to follow other vendors' questionable decisions. It's not
> really that hard to prepare any incompatible dumps you might have.

Really? can you give me an awk or sed pattern that easily disambiguates the
use of NULL as a pseudo column constraint from all of its other uses? I
probably can, but that's not useful if the code is being generated on the
fly by some commerical tool, is it?

My feeling on PostgreSQL, SQL standards, and vendor specific compatibility
options is: "be generous with what you accept, strict with what you
create" or something to that effect. We already have a number of vendor
extension compatiblity features in place[1]. Should all of those be ripped
out as well? If this accepting NULL pseudo constrains _costs us nothing_,
why not do it? I agree that implementing missing SQL92 standard features
is more important, and if this NULL actually interfered with anything
else, I'd see your point: straight to the bit bucket with it. But I don't
understand the resistance to a costless extension. The SQL standards
are certainly not holy writ, especially in regards to what they leave out.

Ross

[1] at least, I _think_ we do. Can't seem to list them, right off hand. I
do know a number of cases where ambigous wording in the standard has
been settled by asking "how do the other DBMSs do it?" As in "What will
an experienced DBA expect?"

-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

"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

On Tue, Nov 30, 1999 at 01:07:22PM -0500, Tom Lane wrote:

<snipped excellent description about the problem with NULL as a
column constraint>

Ah, now I understand. And as I said, if it would cause a problem, now or
in the forseeable future, skip it. I propose dropping Tom's explanation
(or a summary of it) into the FAQ. how about :

------------
Q: Why doesn't PostgreSQL support NULL as a column constraint?

Short A: Because it's not in the SQL92 standard.

Long A:

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

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

I'm using this funtion via a trigger to total up amounts in several fields
(and adding that value into a total field)..


CREATE FUNCTION invoice_total_trigger() RETURNS OPAQUE AS '
BEGIN NEW.total := NEW.fee_membership + NEW.fee_logins + NEW.fee_convention +
NEW.fee_prints+ NEW.fee_hotlines + NEW.fee_postage + NEW.fee_ups +
NEW.fee_late + NEW.fee_other1 + NEW.fee_other2 + NEW.fee_other3 +
NEW.fee_pastdue; RETURN NEW;
END;
' LANGUAGE 'plpgsql';

That's the function, here is the trigger.


CREATE TRIGGER invoice_total_trigger AFTER INSERT OR UPDATE ON invoice
FOR EACH ROW EXECUTE PROCEDURE invoice_total_trigger();


It works for updating but not when you insert a new row. Am I doing it
wrong?

I need the invoice_total_trigger to run every time a row is updated or
inserted..

Thanks guys!
-Mitch

"Mitch Vincent" <mitch@venux.net> writes:
> I'm using this funtion via a trigger to total up amounts in several fields
> (and adding that value into a total field)..

> CREATE TRIGGER invoice_total_trigger AFTER INSERT OR UPDATE ON invoice
> FOR EACH ROW EXECUTE PROCEDURE invoice_total_trigger();

> It works for updating but not when you insert a new row. Am I doing it
> wrong?

Er, don't you want BEFORE INSERT OR UPDATE?

I wouldn't think an AFTER trigger could modify the already-inserted-or-
updated row...
        regards, tom lane

Thanks for the reply Tom (as always)..

I did try both AFTER and BEFORE, neither worked for the insert.. Can you see
anything wrong with the syntax?
I get no errors or anything in the postgres.log, I have debuging set fairly
high too :

StartTransactionCommand
query: insert into invoice
(invoice_number,agencycode,invoice_date,fee_membership,fee_logins,fee_conven
tion,fee_prints,fee_hotlines,fee_postage,fee_ups,fee_late)
values(17623,'PAHO-231','12-01-1999',0.000000,0.000000,5.000000,0.000000,0.0
00000,0.000000,95.000000,0.000000);
ProcessQuery
CommitTransactionCommand

There is an example of one of the inserts, there are 312 done at a time, all
look just like that. It doesn't look like that the trigger is ever getting
called.. Would you agree?

-Mitch



----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mitch Vincent <mitch@venux.net>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, January 04, 2000 10:55 AM
Subject: Re: [SQL] Triggers


> "Mitch Vincent" <mitch@venux.net> writes:
> > I'm using this funtion via a trigger to total up amounts in several
fields
> > (and adding that value into a total field)..
>
> > CREATE TRIGGER invoice_total_trigger AFTER INSERT OR UPDATE ON invoice
> > FOR EACH ROW EXECUTE PROCEDURE invoice_total_trigger();
>
> > It works for updating but not when you insert a new row. Am I doing it
> > wrong?
>
> Er, don't you want BEFORE INSERT OR UPDATE?
>
> I wouldn't think an AFTER trigger could modify the already-inserted-or-
> updated row...
>
> regards, tom lane
>

Mitch Vincent wrote:

> I'm using this funtion via a trigger to total up amounts in several fields
> (and adding that value into a total field)..
>
> CREATE FUNCTION invoice_total_trigger() RETURNS OPAQUE AS '
> ...
>
> That's the function, here is the trigger.
>
> CREATE TRIGGER invoice_total_trigger AFTER INSERT OR UPDATE ON invoice
> FOR EACH ROW EXECUTE PROCEDURE invoice_total_trigger();
>
> It works for updating but not when you insert a new row. Am I doing it
> wrong?
    I wonder why it works on UPDATE at all, it shouldn't. It is an    AFTER ROW trigger and thus, the changed row you
returnshould get    ignored. Use BEFORE INSERT instead.
 
    But that it works on UPDATE indicates some bug somewhere. What    release, OS, etc. are you using?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

The system config is:

P2-400, 256Meg ECC, 1 SCSI WDE9180 ULTRA2, FreeBSD-3.3-Release, 
gcc-2.7.2.1, pg-6.5.1.

That is the development server, our operational server is:

Dual P2-333, 256Meg ECC, 3 SCSI, FreeBSD-3.3-Release, gcc-2.7.2.1, pg-6.5.3.

The trigger is untested on the operational server as of yet.


Matthew (works with Mitch)


At 07:42 PM 1/4/00 +0100, Jan Wieck wrote:
>Mitch Vincent wrote:
>
> > I'm using this funtion via a trigger to total up amounts in several fields
> > (and adding that value into a total field)..
> >
> > CREATE FUNCTION invoice_total_trigger() RETURNS OPAQUE AS '
> > ...
> >
> > That's the function, here is the trigger.
> >
> > CREATE TRIGGER invoice_total_trigger AFTER INSERT OR UPDATE ON invoice
> > FOR EACH ROW EXECUTE PROCEDURE invoice_total_trigger();
> >
> > It works for updating but not when you insert a new row. Am I doing it
> > wrong?
>
>      I wonder why it works on UPDATE at all, it shouldn't. It is an
>      AFTER ROW trigger and thus, the changed row you return should get
>      ignored. Use BEFORE INSERT instead.
>
>      But that it works on UPDATE indicates some bug somewhere. What
>      release, OS, etc. are you using?
>
>Jan
>
>--
>
>#======================================================================#
># It's easier to get forgiveness for being wrong than for being right. #
># Let's break this rule - forgive me.                                  #
>#========================================= wieck@debis.com (Jan Wieck) #
>
>
>
>
>************