Thread: Should we consider empty fields as NULL values when dealing with string columns ?
Should we consider empty fields as NULL values when dealing with string columns ?
From
"Nagib Abi Fadel"
Date:
HI,
let's say we have the following table :
# CREATE TABLE tempo (col1 varchar(3) not null);
CREATE TABLE
CREATE TABLE
# insert INTO tempo VALUES ('');
INSERT 11420541 1
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!='');
"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
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!='');
>
Re: Should we consider empty fields as NULL values when dealing with string columns ?
From
Richard Huxton
Date:
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
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
Re: Should we consider empty fields as NULL values when dealing with string columns ?
From
"Rod K"
Date:
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).
Re: Should we consider empty fields as NULL values when dealing with string columns ?
From
Shridhar Daithankar
Date:
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