Re: NULL value vs. DEFAULT value. - Mailing list pgsql-general

I do not see any NULL exception.

A default value is used whenever a specific value for a column is not
specified.  If whatever is trying to insert into this table is assigning a
"NULL" value to a field the DEFAULT no longer applies but the NOT NULL check
still does.

CREATE TABLE
    Notnullfield DEFAULT 'value' NOT NULL,
    Nullablefield NULL
;

INSERT INTO TABLE (notnullfield, nullablefield) VALUES (null, null); --
fails due to explicit null value for column notnullfield
INSERT INTO TABLE (nullablefield) VALUES (null); -- success with
notnullfield == 'value' since it was not explicitly set and thus the default
was used

>> Should I only specify DEFAULT and drop the NOT NULL constraint?

No way to answer the question with the provided information.  DEFAULT and
NOT NULL are providing different features so whether either, both, or
neither are required depends on what behavior you require.
If you ALWAYS specify values for all columns in a table then DEFAULT is
pointless but NOT NULL insures you are not inserting NULL.

Looking at your model you do have an issue.  It is impossible to have a
shipment with zero (0) mass (unknown yes, zero no).  Personally I would
stick with tri-value logic here and allow NULL for both the value and the
uom.  I would probably try and restrict "uom" to a domain or an enumerated
type as well - or at least a CHECK IN ('lbs','kg').  You will need to take
extra caution with your queries that use these weights BUT trying to perform
calculations on records without a known weight is going to cause problems -
and by using NULL you are more likely to catch any bugs more quickly than if
you use a DEFAULT of 0.0

I did have a thought that maybe the contents weight is being calculated but
I would probably want to include a Boolean (isempty) to the model to
represent that (and then allow 0 to be a valid value for weight).  But even
in that case you still should have some actual "uom" associated with the
zero.  Of course the gross weight includes the contained so again for that a
zero weight is impossible so the above would only apply to the net-weight.

Just some thoughts.

David J.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of James B. Byrne
Sent: Tuesday, March 08, 2011 9:55 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] NULL value vs. DEFAULT value.

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?


--
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general