Violation of NOT NULL
Hi all,
I have a table where I've set two of the columns (varchar()) to be NOT
NULL. However, PostgreSQL allows me to enter rows without valid data for
these columns when I use a Stored Function. Using a std INSERT from psql
generates (as expected) an error.
I don't accept for a minute that PostgreSQL is broken, so can someone tell
me where I'm doing wrong. Here is an excerpt from the script in question:
...
INSERT INTO nhsgps (titleid, forename, surname, surgery, addr1, addr2,
addr3, town, postcode, telno1, telno2, email, url)
VALUES ($2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) ;
GET_DIAGNOSTICS (nRowCount = ROW_COUNT) ;
IF (nRowCount = 0) THEN
RAISE NOTICE \'Error Message\' ;
END IF ;
...
Thanks.
--
Regards,
Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk
"Wm. G. Urquhart" <wgu@wurquhart.co.uk> writes:
I have a table where I've set two of the columns (varchar()) to be NOT
NULL. However, PostgreSQL allows me to enter rows without valid data for
these columns when I use a Stored Function.
I don't really believe this, and I'm certainly not likely to be able to
reproduce it with nothing but an INSERT to go on. Please provide a
*complete* example, preferably in the form of a script that can be fed
to psql to exhibit the problem starting from an empty database.
regards, tom lane
Tom Lane wrote:
"Wm. G. Urquhart" <wgu@wurquhart.co.uk> writes:
I have a table where I've set two of the columns (varchar()) to be NOT
NULL. However, PostgreSQL allows me to enter rows without valid data for
these columns when I use a Stored Function.I don't really believe this, and I'm certainly not likely to be able to
reproduce it with nothing but an INSERT to go on. Please provide a
*complete* example, preferably in the form of a script that can be fed
to psql to exhibit the problem starting from an empty database.
Also, if by chance you are coming over from Oracle and expect an empty
string to be treated as NULL, it is not...
Mike Mascari
mascarm@mascari.com
"Wm. G. Urquhart" wrote:
On Thu, 23 May 2002, Mike Mascari wrote:
<snip/>
Also, if by chance you are coming over from Oracle and expect an empty
string to be treated as NULL, it is not...Mike Mascari
mascarm@mascari.comWell I have a few years of Oracle behind me and it has to said I was
working on that assumption.With that said if '' does != NULL then what does? Just NULL? In my book
NULL means undefined and IMHO you can't get more undefined than ''!I called my stored function passing NULL as a parameter and sure enough
the error was raised. So, what I'm going to have to do now is add code to
either the C++ application or within the script to trap empty $.Oh well.
You could:
CREATE TABLE foo (
value text CHECK (COALESCE(value, '') <> '')
);
if you want to prohibit both NULL and empty text as data...
Hope that helps,
Mike Mascari
mascarm@mascari.com
Import Notes
Reference msg id not found: Pine.LNX.4.44.0205231605330.16744-100000@mailer.wurquhart.co.uk | Resolved by subject fallback
On Thu, 23 May 2002, Mike Mascari wrote:
<snip/>
Also, if by chance you are coming over from Oracle and expect an empty
string to be treated as NULL, it is not...Mike Mascari
mascarm@mascari.com
Well I have a few years of Oracle behind me and it has to said I was
working on that assumption.
With that said if '' does != NULL then what does? Just NULL? In my book
NULL means undefined and IMHO you can't get more undefined than ''!
I called my stored function passing NULL as a parameter and sure enough
the error was raised. So, what I'm going to have to do now is add code to
either the C++ application or within the script to trap empty $.
Oh well.
--
Regards,
Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk
With that said if '' does != NULL then what does? Just NULL? In my book
NULL means undefined and IMHO you can't get more undefined than ''!
To me, '' is very defined. You're explicitly telling something to be
set to the empty string. It has a definite value - "empty string".
NULL implies that you have no information whatsoever to determine what
the value is.
That's just me. I was always bothered that Oracle did consider empty
strings to be NULLs. =)
-Fran
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Wm. G. Urquhart
Sent: Thursday, May 23, 2002 11:19 AM
To: Mike Mascari
Cc: PostgreSQL General Forum
Subject: Re: [GENERAL] Violation of NOT NULLOn Thu, 23 May 2002, Mike Mascari wrote:
<snip/>
Also, if by chance you are coming over from Oracle and expect an empty
string to be treated as NULL, it is not...Mike Mascari
mascarm@mascari.comWell I have a few years of Oracle behind me and it has to said I was
working on that assumption.With that said if '' does != NULL then what does? Just NULL? In my book
NULL means undefined and IMHO you can't get more undefined than ''!
NULL is unknown. '' is empty. If I don't have a middle name, then my middle
name = ''. If I might have one, but you don't know what it is, then my
middle name IS NULL. (Which is, subtly, not the same thing as = NULL, as
NULL is a state or condition, rather than a value. Nothing is = NULL, not
even NULL itself.)
- J.
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant