title: Restrict insert into column with default value
body: <p>Taking the following schema as an example:</p>
  <pre><code>CREATE TABLE public.&quot;RiskHeaders&quot;
  (
      &quot;UID&quot; uuid NOT NULL DEFAULT uuid_generate_v4(),
      &quot;Name&quot; character varying(35) NOT NULL
  )
  </code></pre>
  <p><strong>Is it possible to prevent <code>INSERT</code> statements including values for the <code>UID</code> column?</strong></p>
  <p>For avoidance of doubt, I wish for all values within the <code>UID</code> column to be randomly generated via the default value function.</p>
  <p>I had hoped that I could use an INSERT trigger to see whether <code>UID</code> had been populated but that doesn't appear to be possible:</p>
  <pre><code>BEGIN
      IF TG_OP = 'INSERT' THEN
          IF TG_WHEN = 'BEFORE' THEN
              RAISE NOTICE 'Before: Old = %    New = %', OLD.&quot;UID&quot;, NEW.&quot;UID&quot;;
          ELSIF TG_WHEN = 'AFTER' THEN
              RAISE NOTICE 'After: Old = %    New = %', OLD.&quot;UID&quot;, NEW.&quot;UID&quot;;
          END IF;
      END IF;
      RETURN NEW;
  END;
  </code></pre>
  <p>Running <code>INSERT INTO &quot;RiskHeaders&quot;(&quot;Name&quot;) VALUES ('Test');</code> gives the following console output:</p>
  <pre><code>NOTICE:  Before: Old = &lt;NULL&gt;    New = ccc6757f-d1a2-4849-828d-017d2b738c9d
  NOTICE:  After: Old = &lt;NULL&gt;    New = ccc6757f-d1a2-4849-828d-017d2b738c9d
  </code></pre>
  <p>Running <code>INSERT INTO &quot;RiskHeaders&quot;(&quot;UID&quot;, &quot;Name&quot;) VALUES (uuid_generate_v4(), 'Test1');</code> gives the same output (albeit with a different UUID).</p>
  <p>Thank you in advance for any suggestions and hopefully this isn't too daft a question!</p>
  <hr />
  <p>Many thanks for all the suggestions!</p>
  <p>Given my (reckless?) determination to keep the <code>UID</code> column as a <code>uuid</code> type and prevent the user from specifying any values on <code>INSERT</code>, I have come up with the following work-around(/bodge):</p>
  <p>I am now using the following schema:</p>
  <pre><code>CREATE TABLE public.&quot;RiskHeaders&quot;
  (
      &quot;UID&quot; uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'::uuid,
      &quot;Name&quot; character varying(35) NOT NULL
  }
  </code></pre>
  <p>Taking @stickybit's suggestion to use a trigger... I have come up with a trigger to the effect of:</p>
  <pre><code>    IF TG_OP = 'INSERT' THEN
          IF TG_WHEN = 'BEFORE' THEN
              IF NEW.&quot;UID&quot; &lt;&gt; '00000000-0000-0000-0000-000000000000'::uuid THEN
                  RAISE EXCEPTION 'A UID cannot be specified when creating a risk.';
              ELSE
                  NEW.&quot;UID&quot; := uuid_generate_v4();
                  RETURN NEW;             
              END IF;
              ...
          END IF;
      END IF;
      ...
  </code></pre>
  <p>The drawbacks from this are:</p>
  <ul>
  <li>The schema doesn't make it clear that UIDs are actually being randomly assigned.</li>
  <li>The user could specify a zero UID which would then get silently replaced.</li>
  </ul>