Re: DEFERRABLE NOT NULL constraint - Mailing list pgsql-general

Getting back to the OP (Andreas):

On Tuesday, February 5, 2013 at 20:22, Andreas Joseph Krogh wrote:

>P=C3=A5 tirsdag 05. februar 2013 kl. 09:59:54, skrev Albe Laurenz:
>Andreas Joseph Krogh wrote: ...
>>Are there any plans to make NOT NULL constraints deferrable so
>>one can avoid the trigger "boilerplate"?
>
>Not that I know of.
>
>There's an entry in the TODO list that recognizes that it would
>be desirable to make NOT NULL a regular constraint (you can do
>that today by using CHECK (col IS NOT NULL) instead).
>
>But CHECK constraints are also not deferrable...
>
....
>Is there any "I want to sponsor development of <feature-X> with=20
>$xxx" mechanism? =C2=A0

On Thursday, February 7, 2013 at 18:45, Albe Laurenz wrote:

>... the standard caters for deferrable NOT NULL constraints.
>
So, notwithstanding the many expressions of personal preference=20
and several suggested 'work arounds' needed to compensate for=20
this implied SQL compliance failure, there seems to be no good=20
reason why this 'entry in the TODO list' couldn't be sponsored=20
for development.

But I feel I have missed something here.

Referring to:
<http://www.postgresql.org/docs/current/static/sql-createtable.html>

     where column_constraint is:

     [ CONSTRAINT constraint_name ]
     { NOT NULL |
       NULL |
       CHECK ( expression ) [ NO INHERIT ] |
       DEFAULT default_expr |
       UNIQUE index_parameters |
       PRIMARY KEY index_parameters |
       REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL |=20
MATCH PARTIAL | MATCH SIMPLE ]
         [ ON DELETE action ] [ ON UPDATE action ] }
     [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED |=20
INITIALLY IMMEDIATE ]

     and table_constraint is:

     [ CONSTRAINT constraint_name ]
     { CHECK ( expression ) [ NO INHERIT ] |
       UNIQUE ( column_name [, ... ] ) index_parameters |
       PRIMARY KEY ( column_name [, ... ] ) index_parameters |
       EXCLUDE [ USING index_method ] ( exclude_element WITH=20
operator [, ... ] )
       index_parameters [ WHERE ( predicate ) ] |
       FOREIGN KEY ( column_name [, ... ] )
         REFERENCES reftable [ ( refcolumn [, ... ] ) ]
         [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON=20
DELETE action ]
         [ ON UPDATE action ] }
     [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED |=20
INITIALLY IMMEDIATE ]

CHECK constraints, NOT NULL constraints and FOREIGN KEY=20
constraints all look very deferrable in this definition. If=20
that's the case, why are we having this discussion if the=20
requested functionality/compliance is already present? (As I=20
have said already) I really must have missed something so am=20
standing by for the 'gotcha'... please supply :)

Regards
Gavan Schneider