Обсуждение: 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) # > > > > >************