>From the ANSI/ISO SQL Standard: "3) The comparison of two character strings is determined as follows: a) Let CS be the collating sequence indicated in Subclause 4.2.3, ''Rules determining collating sequence usage'', based on the declared types of the two character strings. b) If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad characters, where the pad character is chosen based on CS. If CS has the NO PAD characteristic, then the pad character is an implementation-dependent character different from any character in the character set of X and Y that collates less than any string under CS. Otherwise, the pad character is a <space>. c) The result of the comparison of X and Y is given by the collating sequence CS." > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Guy Rouillier > Sent: Wednesday, October 19, 2005 10:56 AM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: [pgsql-advocacy] Oracle buys Innobase > > Richard_D_Levine@xxxxxxxxxxxx wrote: > > Yep. It is not just limited to empty strings; An all blank string, > > no matter the number of characters, is stored as NULL. And a > > I'm no big Oracle fan; I'm trying to convince my company to convert a > major database to PG. But I can't reproduce what you are saying here. > What version of Oracle are you using? I just tried this with 9i, and it > properly stores the entered number of spaces into the DB. Table t1 is > defined with a single varchar2(10) column: > > INSERT INTO t1 VALUES (' '); > SELECT LENGTH(f1) FROM t1; > => 2 > > > corollary to that idiocy is that a string with two blank characters > > is not equal to a string with a single blank character in Oracle. 'a > > ' is not equal to 'a '. 'a ' is not equal to 'a'. > > I certainly hope not. If PG is doing that, it's doing the wrong thing. > Would you expect 'abc' to be equal to 'a'? Why then would you expect 'a > ' to be equal to 'a'? A space character is as valid a character as 'b' > and 'c'. If the user chooses to ignore spaces, he/she can do that with > trim functions, but no DBMS should do that blindly. > > > Port that to another database. Seen the JOIN syntax? *sigh* > > I believe you're referring to the 8i (+) syntax? 9i supports regular > outer join syntax. > > -- > Guy Rouillier > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
