Thread: Documentation clairification?, CHECK constraints
Re: http://www.postgresql.org/docs/7.4/static/sql-createtable.html "The CHECK clause specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed." But SQL has 3 valued logic (strictly speaking, not boolean). It seems that the CHECK clases fail only when the expression result is FALSE, and pass when the result is either TRUE or UNKNOWN. Yes? Should this be clarified in the documentation? Somewhere along the line IIRC the rule that UNKNOWN always causes the code inside the THEN conditional to be skipped, so explicit tests for NULL are often needed as a 'special case'. Not that this has much to do with CHECK clauses, but I don't see the conditionals page for plpgsql saying anything about UNKNOWN values. Maybe the section to describe how conditionals treat UNKNOWN is 9.1, conditional operators? Karl <kop@meme.com> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein
"Karl O. Pinc" <kop@meme.com> writes:
> But SQL has 3 valued logic (strictly speaking, not boolean). It
> seems that the CHECK clases fail only when the expression result
> is FALSE, and pass when the result is either TRUE or UNKNOWN.
Correct --- this is per spec.
> Yes? Should this be clarified in the documentation?
Sure, send a documentation patch.
regards, tom lane
On 2004.03.31 10:53 Tom Lane wrote:
> "Karl O. Pinc" <kop@meme.com> writes:
> > But SQL has 3 valued logic (strictly speaking, not boolean). It
> > seems that the CHECK clases fail only when the expression result
> > is FALSE, and pass when the result is either TRUE or UNKNOWN.
>
> Correct --- this is per spec.
>
> > Yes? Should this be clarified in the documentation?
>
> Sure, send a documentation patch.
--- create_table.sgml Wed Mar 31 12:57:02 2004
+++ create_table.sgml.new Wed Mar 31 13:16:55 2004
@@ -383,11 +383,14 @@
<para>
The <literal>CHECK</> clause specifies an expression producing a
Boolean result which new or updated rows must satisfy for an
- insert or update operation to succeed. A check constraint
- specified as a column constraint should reference that column's
- value only, while an expression appearing in a table constraint
- may reference multiple columns.
+ insert or update operation to succeed. Expressions evaluating
+ to TRUE or UNKNOWN succeed. Should any row of an insert or
+ update operation produce a FALSE result an error exception is
+ raised and the insert or update does not alter the database. A
+ check constraint specified as a column constraint should
+ reference that column's value only, while an expression
+ appearing in a table constraint may reference multiple columns.
</para>
<para>
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
OK, patch applied. Thanks. --------------------------------------------------------------------------- Karl O. Pinc wrote: > > On 2004.03.31 10:53 Tom Lane wrote: > > "Karl O. Pinc" <kop@meme.com> writes: > > > But SQL has 3 valued logic (strictly speaking, not boolean). It > > > seems that the CHECK clases fail only when the expression result > > > is FALSE, and pass when the result is either TRUE or UNKNOWN. > > > > Correct --- this is per spec. > > > > > Yes? Should this be clarified in the documentation? > > > > Sure, send a documentation patch. > > --- create_table.sgml Wed Mar 31 12:57:02 2004 > +++ create_table.sgml.new Wed Mar 31 13:16:55 2004 > @@ -383,11 +383,14 @@ > <para> > The <literal>CHECK</> clause specifies an expression producing a > Boolean result which new or updated rows must satisfy for an > - insert or update operation to succeed. A check constraint > - specified as a column constraint should reference that column's > - value only, while an expression appearing in a table constraint > - may reference multiple columns. > + insert or update operation to succeed. Expressions evaluating > + to TRUE or UNKNOWN succeed. Should any row of an insert or > + update operation produce a FALSE result an error exception is > + raised and the insert or update does not alter the database. A > + check constraint specified as a column constraint should > + reference that column's value only, while an expression > + appearing in a table constraint may reference multiple columns. > </para> > <para> > > Karl <kop@meme.com> > Free Software: "You don't pay back, you pay forward." > -- Robert A. Heinlein > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073