Обсуждение: 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

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

"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.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.

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

>
>
>>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 NULL
>
>
> 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 ''!

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