Ross, You're exactly right, I re-created my table with text(may not seem elegant), the problem is solved. Thanks to all of your advice, I can now go on with my life with PostgreSQL. :)
jjw 8/25/2002 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ross J. Reedstrom Sent: Saturday, August 24, 2002 9:28 PM To: Jiaqing Wang Cc: [EMAIL PROTECTED] Subject: Re: [SQL] weird situation, BUG or I'm not doing it right On Sat, Aug 24, 2002 at 10:56:31PM -0700, Jiaqing Wang wrote: > Hello, > > I found below situation weird, it seems to me a bug. > > backend=> select * from valid_addr where state_abrev=upper('pr'); > zip_code | city_name | state_abrev > ----------+-----------+------------- > (0 rows) > > while "select * from valid_addr where state_abrev='PR';" produces following > output > <20 lines of output> You left out the critical piece: what's the schema for the table valid_addr? I'll deduce that the column "state_abrev" is defined as something like 'char(4)'. It needs to be either char(2) (if _all_ state abbreviations are guarenteed to be only 2) or as text. fixed with char fields are padded with blanks. Not a bug, but an feature of the SQL standard. Ross ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
