Message-ID: <029c01c6c228$8e8e5210$0600a8c0@Wilbur>
From: "Julian Scarfe" <julian@avbrief.com>
To: <pgsql-sql@postgresql.org>
Subject: NULL becomes default
Date: Thu, 17 Aug 2006 19:11:21 +0100
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit

A surrogate key has been introduced on a table with modifiers:

   Column    |            Type             | 
Modifiers
-------------+-----------------------------+------------------------------------------------------------
 new_id    | integer                     | not null default 
nextval(('some_id_seq'::text)::regclass)

However, some legacy software inserts into the table by first getting column 
names and then composing an INSERT for all the columns, filling in any 
undefined values with NULLs.

Thus when it attempts to insert a row I get a:

ERROR:  null value in column "new_id" violates not-null constraint

Thus I would like NULLs in such INSERTs to be treated as DEFAULT rather than 
violating the constraint.  Is there an easy way to do that at the database 
level?

Thanks

Julian