pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Should we consider empty fields as NULL values when
8+ messages / 8 participants
[nested] [flat]
* Re: Should we consider empty fields as NULL values when
@ 2003-12-11 14:15 Chris Travers <chris@travelamericas.com>
parent: Nagib Abi Fadel <nagib.abi-fadel@usj.edu.lb>
5 siblings, 0 replies; 8+ messages in thread
From: Chris Travers @ 2003-12-11 14:15 UTC (permalink / raw)
To: Nagib Abi Fadel <nagib.abi-fadel@usj.edu.lb>; +Cc: generalpost <pgsql-general@postgresql.org>; pgsql-hackers
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!='');
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Should we consider empty fields as NULL values when dealing with string columns ?
@ 2003-12-11 14:36 Nagib Abi Fadel <nagib.abi-fadel@usj.edu.lb>
0 siblings, 6 replies; 8+ messages in thread
From: Nagib Abi Fadel @ 2003-12-11 14:36 UTC (permalink / raw)
To: generalpost <pgsql-general@postgresql.org>; pgsql-hackers
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!='');
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Should we consider empty fields as NULL values when
@ 2003-12-11 14:52 Doug McNaught <doug@mcnaught.org>
parent: Nagib Abi Fadel <nagib.abi-fadel@usj.edu.lb>
5 siblings, 0 replies; 8+ messages in thread
From: Doug McNaught @ 2003-12-11 14:52 UTC (permalink / raw)
To: Nagib Abi Fadel <nagib.abi-fadel@usj.edu.lb>; +Cc: generalpost <pgsql-general@postgresql.org>; pgsql-hackers
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [HACKERS] Should we consider empty fields as NULL values when dealing with string columns ?
@ 2003-12-11 14:52 Dave Page <dpage@vale-housing.co.uk>
0 siblings, 0 replies; 8+ messages in thread
From: Dave Page @ 2003-12-11 14:52 UTC (permalink / raw)
To: Nagib Abi Fadel <nagib.abi-fadel@usj.edu.lb>; generalpost <pgsql-general@postgresql.org>; pgsql-hackers
_____
From: Nagib Abi Fadel [mailto:nagib.abi-fadel@usj.edu.lb]
Sent: 11 December 2003 14:37
To: generalpost; developPost
Subject: [HACKERS] 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.
As it should. NULL != ''. In other words, NULL is no value at all,
whilst '' is an empty string which is a value, albeit one of zero
length.
Regards, Dave.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Should we consider empty fields as NULL values when dealing with string columns ?
@ 2003-12-11 15:00 Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
parent: Nagib Abi Fadel <nagib.abi-fadel@usj.edu.lb>
5 siblings, 0 replies; 8+ messages in thread
From: Shridhar Daithankar @ 2003-12-11 15:00 UTC (permalink / raw)
To: Nagib Abi Fadel <nagib.abi-fadel@usj.edu.lb>; generalpost <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Should we consider empty fields as NULL values when dealing with string columns ?
@ 2003-12-11 15:34 Richard Huxton <dev@archonet.com>
parent: Nagib Abi Fadel <nagib.abi-fadel@usj.edu.lb>
5 siblings, 0 replies; 8+ messages in thread
From: Richard Huxton @ 2003-12-11 15:34 UTC (permalink / raw)
To: Nagib Abi Fadel <nagib.abi-fadel@usj.edu.lb>; generalpost <pgsql-general@postgresql.org>; pgsql-hackers
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Should we consider empty fields as NULL values when
@ 2003-12-11 15:48 Tino Wildenhain <tino@wildenhain.de>
parent: Nagib Abi Fadel <nagib.abi-fadel@usj.edu.lb>
5 siblings, 0 replies; 8+ messages in thread
From: Tino Wildenhain @ 2003-12-11 15:48 UTC (permalink / raw)
To: Nagib Abi Fadel <nagib.abi-fadel@usj.edu.lb>; +Cc: generalpost <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Should we consider empty fields as NULL values when dealing with string columns ?
@ 2003-12-11 16:00 Rod K <rod@23net.net>
parent: Nagib Abi Fadel <nagib.abi-fadel@usj.edu.lb>
5 siblings, 0 replies; 8+ messages in thread
From: Rod K @ 2003-12-11 16:00 UTC (permalink / raw)
To: Nagib Abi Fadel <nagib.abi-fadel@usj.edu.lb>; generalpost <pgsql-general@postgresql.org>; pgsql-hackers
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).
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-12-11 16:00 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-11 14:36 Should we consider empty fields as NULL values when dealing with string columns ? Nagib Abi Fadel <nagib.abi-fadel@usj.edu.lb>
2003-12-11 14:15 ` Chris Travers <chris@travelamericas.com>
2003-12-11 14:52 ` Doug McNaught <doug@mcnaught.org>
2003-12-11 15:00 ` Re: Should we consider empty fields as NULL values when dealing with string columns ? Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-12-11 15:34 ` Re: Should we consider empty fields as NULL values when dealing with string columns ? Richard Huxton <dev@archonet.com>
2003-12-11 15:48 ` Tino Wildenhain <tino@wildenhain.de>
2003-12-11 16:00 ` Re: Should we consider empty fields as NULL values when dealing with string columns ? Rod K <rod@23net.net>
2003-12-11 14:52 Re: [HACKERS] Should we consider empty fields as NULL values when dealing with string columns ? Dave Page <dpage@vale-housing.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox