Thread: 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
On Mar 8, 2011, at 7:54 AM, James B. Byrne wrote: > My question is: Why am I getting a NULL exception? Because you're trying to insert NULL explicitly? -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
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
On 08/03/2011 14:54, James B. Byrne wrote: > My question is: Why am I getting a NULL exception? Should I only > specify DEFAULT and drop the NOT NULL constraint? Good question. Are you trying to insert an explicit NULL? Can you show us your INSERT statement? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
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
On Tue, March 8, 2011 10:09, Scott Ribe wrote: > On Mar 8, 2011, at 7:54 AM, James B. Byrne wrote: > >> My question is: Why am I getting a NULL exception? > > Because you're trying to insert NULL explicitly? Yes, that is the problem. Evidently RoR's ActiveRecord helpfully converts a string containing nought but spaces to nil when a numeric value is required for the column type. The problem arises with a single unit record received from the government system that has a UOM code provided but the associated decimal value field is blank. Since the default is zero in our DB I have altered our load program to coerce a value of zero for strings containing only spaces destined for numeric columns. But, it feels ugly. I would really like to be able to coerce nils to some value on a column by column basis on the DBMS side. This is not really a DEFAULT value and I do not know what I would call it if such a thing did exist. I suppose a trigger and function is called for. Thanks for the help. -- *** 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