Re: [HACKERS] Question about Oracle compatibility
At 09:59 AM 11/28/00 +, Pete Forman wrote: Mario Weilguni writes: This gets really bad when the actual data is coming from a webinterface, I've to handle 2 different queries for the case empty string and non-empty string. In their documentation both Oracle 7 and 8 state: Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as NULLs. Yeah, but this is harder than it sounds! NULL and '' are indistinguishable in queries, so how do you treat them differently? Has to be in the application code, I guess. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Question about Oracle compatibility
Mario Weilguni [EMAIL PROTECTED] writes: In Oracle, empty strings and null are basicly the same, Are you sure about that? It'd be a pretty major failure to comply with SQL standard semantics, if so. SQL92 3.1 (Definitions): null value (null): A special value, or mark, that is used to indicate the absence of any data value. SQL92 4.1 (Data types) A null value is an implementation-dependent special value that is distinct from all non-null values of the associated data type. There is effectively only one null value and that value is a member of every SQL data type. There is no literal for a null value, although the keyword NULL is used in some places to indicate that a null value is desired. There is no room there for equating NULL with an empty string. I also read the last-quoted sentence to specifically forbid treating the literal '' as NULL. regards, tom lane
Re: [HACKERS] Question about Oracle compatibility
On Mon, 27 Nov 2000, Mario Weilguni wrote: Sorry if I'm posting to the wrong list, but I don't know which list is appropriate for this question. I've a question concerning compatibilty Postgres - Oracle. In Oracle, empty strings and null are basicly the same, but it does not seem to be under Postgres, making migration a pain. Actually, they aren't the same at all under Oracle or under Postgres. A null represents a lack of data, whereas an empty string is represents data of zero length and zero content. Null is a state and not a value. What you are probably seeing is a difference in table layout that sets a default value of '' for the particular column you're touching. You can have postgres do the same by specifying DEFAULT '' when you create your table (or you could ALTER it in..). Null values are actually quite important because they tell you when you don't have data. An empty tring means something is there, whereas a null in the same place means complete absense of all data. Hope this helps. Thanks Alex Example: ORACLE: select id from anytable where field=''; POSTGRES: select id from anytable where field='' or field is null; Or another example: The oracle query update anytable set adatefiled='' fails in Postgres, I've to write update anytable set adatefield=null; That seems really weird. This gets really bad when the actual data is coming from a webinterface, I've to handle 2 different queries for the case empty string and non-empty string. Is there a better way to achieve this? Thanks! Best regards, Mario Weilguni -- Alex G. Perel -=- AP5081 [EMAIL PROTECTED] -=- [EMAIL PROTECTED] play -=- work Disturbed Networks - Powered exclusively by FreeBSD == The Power to Serve -=- http://www.freebsd.org/
Re: [HACKERS] Question about Oracle compatibility
At 12:39 PM 11/27/00 -0500, Alex Perel wrote:
On Mon, 27 Nov 2000, Mario Weilguni wrote:
Sorry if I'm posting to the wrong list, but I don't know which list is
appropriate for this question.
I've a question concerning compatibilty Postgres - Oracle. In Oracle,
empty strings and null are basicly the same, but it does not seem to
be under Postgres, making migration a pain.
Actually, they aren't the same at all under Oracle or under Postgres.
A null represents a lack of data, whereas an empty string is represents
data of zero length and zero content. Null is a state and not a value.
Unfortunately Mario's entirely correct (I use Oracle...)
insert into foo (some_string) values ('');
will insert a NULL, not an empty string, into the column some_string.
What you are probably seeing is a difference in table layout that sets
a default value of '' for the particular column you're touching. You can
have postgres do the same by specifying DEFAULT '' when you create your
table (or you could ALTER it in..).
Using "DEFAULT ''" might help some, but he specifically mentioned inserting
form data from a web page, and in this case he'll have to check the string
and explicitly insert NULL (or write a trigger for each table that does
the check and the resulting massage of the value) or rewrite his queries
to treat empty string as being the same as NULL explicitly.
Null values are actually quite important because they tell you when you
don't have data. An empty tring means something is there, whereas a null
in the same place means complete absense of all data.
Absolutely right, and Oracle's misimplementation truly sucks.
- Don Baccus, Portland OR [EMAIL PROTECTED]
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.
Re: [HACKERS] Question about Oracle compatibility
On Mon, 27 Nov 2000, Don Baccus wrote:
Actually, they aren't the same at all under Oracle or under Postgres.
A null represents a lack of data, whereas an empty string is represents
data of zero length and zero content. Null is a state and not a value.
Unfortunately Mario's entirely correct (I use Oracle...)
insert into foo (some_string) values ('');
will insert a NULL, not an empty string, into the column some_string.
I stand corrupted. I didn't remember this behavior. :/
What you are probably seeing is a difference in table layout that sets
a default value of '' for the particular column you're touching. You can
have postgres do the same by specifying DEFAULT '' when you create your
table (or you could ALTER it in..).
Using "DEFAULT ''" might help some, but he specifically mentioned inserting
form data from a web page, and in this case he'll have to check the string
and explicitly insert NULL (or write a trigger for each table that does
the check and the resulting massage of the value) or rewrite his queries
to treat empty string as being the same as NULL explicitly.
Might be easiest to feed the data through a simple stored proc. Doesn't take
long at all to whip something together for the purpose..
--
Alex G. Perel -=- AP5081
[EMAIL PROTECTED] -=- [EMAIL PROTECTED]
play -=- work
Disturbed Networks - Powered exclusively by FreeBSD
== The Power to Serve -=- http://www.freebsd.org/
