Re: Should we consider empty fields as NULL values when

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!='');
>