Horton

Re: DEFERRABLE NOT NULL constraint

Andreas Joseph Krogh <andreak@officenet.no>

View thread View on postgr.es
From: Andreas Joseph Krogh <andreak@officenet.no>
To: pgsql-general@postgresql.org
Date: 2013-02-05T09:22:18Z
Lists: pgsql-general

Attachments

<div>På tirsdag 05. februar 2013 kl. 09:59:54, skrev Albe Laurenz &lt;<a href="mailto:laurenz.albe@wien.gv.at" target="_blank">laurenz.albe@wien.gv.at</a>&gt;:</div>

<blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div style="display:inline; font-family: monospace; font-size: 12px;">Andreas Joseph Krogh wrote:<br>
&gt; It's currently (9.2) not possible to define DEFERRABLE NOT NULL constraints. Meaning the following is<br>
&gt; not valid:<br>
&gt;<br>
&gt; CREATE TABLE my_table(<br>
&gt; id varchar PRIMARY KEY,<br>
&gt; stuff_id BIGINT NOT NULL DEFERRABLE INITIALLY DEFERRED<br>
&gt; );<br>
&gt;<br>
&gt; While it's possible to define a trigger to enforce this, like this:<br>
&gt;<br>
&gt; CREATE CONSTRAINT TRIGGER my_table_t AFTER INSERT OR UPDATE ON onp_crm_relation DEFERRABLE INITIALLY<br>
&gt; DEFERRED<br>
&gt; FOR EACH ROW EXECUTE PROCEDURE my_table_check_stuff_id_nn_tf();<br>
&gt;<br>
&gt; And have the my_table_check_stuff_id_nn_tf() raise an exception if &quot;stuff_id&quot; is null.<br>
&gt;<br>
&gt; Having deferrable constraints on FKs and UKs is really nice and when working with ORMs it's almost<br>
&gt; impossible to not use this feature.<br>
&gt;<br>
&gt; Are there any plans to make NOT NULL constraints deferrable so one can avoid the trigger<br>
&gt; &quot;boilerplate&quot;?<br>
<br>
Not that I know of.<br>
<br>
There's an entry in the TODO list that recognizes that it would<br>
be desirable to make NOT NULL a regular constraint (you can do<br>
that today by using CHECK (col IS NOT NULL) instead).<br>
<br>
But CHECK constraints are also not deferrable...</div>
</blockquote>

<div> </div>

<div>+100 for having NOT NULL and CHECK-constraints deferrable:-)</div>

<div> </div>

<div>Is there any &quot;I want to sponsor development of &lt;feature-X&gt; with $xxx&quot; mechanism?</div>

<div> </div>

<div class="origo-email-signature">--<br>
Andreas Joseph Krogh &lt;andreak@officenet.no&gt;      mob: +47 909 56 963<br>
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no<br>
Public key: http://home.officenet.no/~andreak/public_key.asc</div>

<div> </div>