Re: NULL value vs. DEFAULT value.

On 8 March 2011 20:24, James B. Byrne <byrnejb@harte-lyne.ca> wrote:
> version = postgresql-8.4.4-2PGDG.el5.src.rpm
>
> I am seeing this problem and I cannot explain why it is happening.
> Evidently I misapprehend something about the interaction of NOT NULL
> and DEFAULT.  If someone could tell me what the actual case is I
> would appreciate it very much.
>
> The table definition looks like this:
> CREATE TABLE ca_customs_shipments (
>    id integer NOT NULL,
> .  .  .
>    weight_mass_gross numeric(14,4) DEFAULT 0.0 NOT NULL,
>    weight_mass_gross_uom character varying(3)
>      DEFAULT '   '::character varying NOT NULL,
>    weight_mass_net numeric(14,4) DEFAULT 0 NOT NULL,
>    weight_mass_net_uom character varying(3)
>      DEFAULT '   '::character varying NOT NULL,
> .  .  .
> );
>
>
> My question is:  Why am I getting a NULL exception?  Should I only
> specify DEFAULT and drop the NOT NULL constraint?

If you pass in a NULL to a column with a NOT NULL and a DEFAULT, the
DEFAULT won't take effect as you've already passed the value, even
though it's NULL, and it would produce the error.

Can you provide an example of a statement which you see this in?

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company