Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2A866475EDC; Sat, 11 Jan 2003 23:45:56 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 7BF41475C5F; Sat, 11 Jan 2003 23:45:55 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0C4jw5u025369; Sat, 11 Jan 2003 23:45:59 -0500 (EST) To: Hale@halepringle.com Cc: pgsql-jdbc@postgresql.org, pgsql-sql@postgresql.org Reply-To: pgsql-sql@postgresql.org Subject: Re: Select * from users WHERE upper(lastName) = upper('Pringle') In-reply-to: References: Comments: In-reply-to "Hale Pringle" message dated "Fri, 10 Jan 2003 18:45:43 -0500" Date: Sat, 11 Jan 2003 23:45:58 -0500 Message-ID: <25368.1042346758@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/134 X-Sequence-Number: 5984 "Hale Pringle" writes: > In a situation where a column names lastname is char(40): > SELECT * FROM users WHERE lastname = 'Pringle' returns one row. > SELECT * FROM users WHERE UPPER(lastname) = UPPER('Pringle') will return > zero rows. > SELECT * FROM users WHERE TRIM(UPPER(lastname) = TRIM(UPPER('Pringle')) > will return one row. lastname is actually 'Pringle ' because of char(N)'s implicit space-padding. When you compare it to an untyped literal 'Pringle', the comparison is assumed to be done under the rules of the char(N) datatype, in which trailing spaces are not significant --- so you get TRUE. When you feed lastname to UPPER(), the result is of type TEXT, because we only have one form of UPPER() and it takes and returns TEXT. And trailing spaces *are* significant according to TEXT's equality operator. So your second case reduces to 'PRINGLE '::text = 'PRINGLE'::text which is false. There has been some talk of altering the coercion rules so that CHAR(n)-to-TEXT coercion is not direct but implicitly invokes rtrim() to get rid of the trailing spaces. This would cause your example to be effectively SELECT * FROM users WHERE UPPER(RTRIM(lastname)) = UPPER('Pringle') which would avoid most of the surprise factor. There are probably still some unpleasant corner cases in this approach, though. In the meantime: my recommendation is to avoid char(N) like the plague, except in cases where the data naturally has a fixed width (US state postal abbreviations, for example, are inherently char(2)). You should be using varchar(40) or text for lastname. char(40) loses on efficiency, storage space, and every other dimension as well as this one. regards, tom lane PS: this is not a JDBC issue, but a backend issue. If you want to discuss it further, please respect the followup-to: pgsql-sql.