Should we consider empty fields as NULL values when dealing with string columns ?
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!='');
"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 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 1the 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
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
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).