
On Thu, 2003-12-11 at 22:36, Nagib Abi Fadel wrote:
> HI,
> let's say we have the following table :
>  
> # CREATE TABLE tempo (col1 varchar(3) not null);
> CREATE TABLE
> 
> # insert INTO tempo VALUES ('');
> INSERT 11420541 1
>  
> the insert command works. 
>  
> The issue is that since the column col1 is defined as character with
> not null attribute,
> shouldn't we deny such inserts (i mean inserting empty fields)???
> (PS: i am using postresql 7.3.2)

NULL has a special meaning and CAST(NULL, string) != ''

Remember that NULL is a special value and does not equate to any other
value.  For example you may KNOW that the value of a string is '', but
if you don't know what the value is, then NULL is the value which
represents that unknown.

For this reason, 
NULL || 'Mystring' IS NULL
'' || 'MyString' = 'MyString'

In the first case, we are appending 'Mystring' to an unknown string
(hence the result is unknown), and in the second, we append 'MyString'
to an empty string.  Hence the value is the same.

>  
> When using script languages (like PHP) if by mistake the variable is
> not defined such insert is possible (let's say we have a variable
> $col1_value and after a long day of work we make a mistake and write
> it $col_value).

The only way to handle this is to write your own routines to check the
values and substitute as appropriate.  That is not the answer you were
looking for, but...

For example (PHP):

function db_quote($db_var){
	if ($db_var === NULL){
		return 'NULL';
	} else {
		return "'$db_var'";
	}
}

This will enclose your variable with single-quotes unless it is not set
in which case it will return a string, "NULL" which can be used in your
database queries.

Best Wishes,
Chris Travers

>  
> This "problem" is solved by adding the constraint:
>  ALTER TABLE tempo add constraint col1_check check(col1!='');
>  


11 Dec 2003 22:15:57 +0800
Chris Travers <chris@travelamericas.com>
Re: Should we consider empty fields as NULL values when
HI,
let's say we have the following table :

# CREATE TABLE tempo (col1 varchar(3) not null);
CREATE TABLE

# insert INTO tempo VALUES ('');
INSERT 11420541 1

the insert command works. 

The issue is that since the column col1 is defined as character with not null attribute,
shouldn't we deny such inserts (i mean inserting empty fields)???
(PS: i am using postresql 7.3.2)

When using script languages (like PHP) if by mistake the variable is not defined such insert is possible (let's say we have a variable $col1_value and after a long day of work we make a mistake and write it $col_value).

This "problem" is solved by adding the constraint:
 ALTER TABLE tempo add constraint col1_check check(col1!='');







HI,
let's say we have the following 
table :
 
# CREATE TABLE tempo (col1 varchar(3) not 
null);CREATE TABLE
# insert INTO tempo VALUES ('');INSERT 11420541 
1
 
the insert command works. 
 
The issue is that since the column col1 is 
defined as character with not null attribute,
shouldn't we deny such inserts (i mean inserting 
empty fields)???
(PS: i am using postresql 7.3.2)
 
When using script languages (like PHP) if by 
mistake the variable is not defined such insert is possible (let's 
say we have a variable $col1_value and after a long day of work we 
make a mistake and write it $col_value).
 
This "problem" is solved by adding the 
constraint:
 ALTER TABLE tempo add constraint 
col1_check check(col1!='');
Thu, 11 Dec 2003 16:36:56 +0200
"Nagib Abi Fadel" <nagib.abi-fadel@usj.edu.lb>
Should we consider empty fields as NULL values when dealing with
	string columns ?
"Nagib Abi Fadel" <nagib.abi-fadel@usj.edu.lb> writes:

>    The issue is that since the column col1 is defined as character with
>    not null attribute,
>
>    shouldn't we deny such inserts (i mean inserting empty fields)???

NULL and the empty string '' are *completely* different things.  If
you want to disallow empty strings as well as NULL, use a CHECK
constraint on that column.

-Doug

Thu, 11 Dec 2003 09:52:25 -0500
Doug McNaught <doug@mcnaught.org>
Re: Should we consider empty fields as NULL values when
On Thursday 11 December 2003 20:06, Nagib Abi Fadel wrote:
> HI,
> let's say we have the following table :
>
> # CREATE TABLE tempo (col1 varchar(3) not null);
> CREATE TABLE
>
> # insert INTO tempo VALUES ('');
> INSERT 11420541 1
>
> the insert command works.
>
> The issue is that since the column col1 is defined as character with not
> null attribute, shouldn't we deny such inserts (i mean inserting empty
> fields)???
> (PS: i am using postresql 7.3.2)

No. Because an empty string is not null.

If you want it to work the way  you describe, I believe you need to set
transform_null_equals to true in postgresql.conf

Check if  postgresql7.3.2 supports it. But remember, relying on this variable 
is not recommended. Probably you should use indicator variables if php 
supports it.

HTH

 Shridhar


Thu, 11 Dec 2003 20:30:15 +0530
Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
Re: Should we consider empty fields as NULL values when dealing with
	string columns ?
On Thursday 11 December 2003 14:36, Nagib Abi Fadel wrote:
> HI,
> let's say we have the following table :
>
> # CREATE TABLE tempo (col1 varchar(3) not null);
> CREATE TABLE
>
> # insert INTO tempo VALUES ('');
> INSERT 11420541 1

I don't have much to add to Doug's reply, but you may want to look at:
http://techdocs.postgresql.org/guides/BriefGuideToNulls

-- 
  Richard Huxton
  Archonet Ltd

Thu, 11 Dec 2003 15:34:09 +0000
Richard Huxton <dev@archonet.com>
Re: Should we consider empty fields as NULL values when dealing with
	string columns ?
Hi,

Nagib Abi Fadel schrieb:
[...]
>  
> When using script languages (like PHP) if by mistake the variable is not 
> defined such insert is possible (let's say we have a variable 
> $col1_value and after a long day of work we make a mistake and write it 
> $col_value).
This is the nastiness with _such_ a scripting language.
Luckyly there are others that throw exceptions in such
a case :-)

SCNR
Tino


Thu, 11 Dec 2003 16:48:28 +0100
Tino Wildenhain <tino@wildenhain.de>
Re: Should we consider empty fields as NULL values when
The rest of your post was answered, but I'll add the following.  You should
set your error_reporting level higher, so you are warned if you are
referencing an unassigned variable.  There is something wrong with your
script if you aren't validating data.
  When using script languages (like PHP) if by mistake the variable is not
defined such insert is possible (let's say we have a variable $col1_value
and after a long day of work we make a mistake and write it $col_value).







The rest of your 
post was answered, but I'll add the following.  You should set your 
error_reporting level higher, so you are warned if you are referencing an 
unassigned variable.  There is something wrong with your script if you 
aren't validating data.

When using script languages (like PHP) if by 
  mistake the variable is not defined such insert is possible (let's 
  say we have a variable $col1_value and after a long day of work we 
  make a mistake and write it $col_value).
Thu, 11 Dec 2003 11:00:29 -0500
"Rod K" <rod@23net.net>
Re: Should we consider empty fields as NULL values when dealing with
	string columns ?