From: Tom Lane <tgl@sss.pgh.pa.us>
To: Hale@halepringle.com
Cc: pgsql-jdbc@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Select * from users WHERE upper(lastName) = upper('Pringle')
Date: Sat, 11 Jan 2003 23:45:58 -0500
Message-ID: <25368.1042346758@sss.pgh.pa.us> (raw)
In-Reply-To: <HIEOJHJBFLIEMFCAOMOEIEMCCIAA.halepringle@yahoo.com>
References: <HIEOJHJBFLIEMFCAOMOEIEMCCIAA.halepringle@yahoo.com>
"Hale Pringle" <halepringle@yahoo.com> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, Hale@halepringle.com, pgsql-jdbc@postgresql.org
Subject: Re: Select * from users WHERE upper(lastName) = upper('Pringle')
In-Reply-To: <25368.1042346758@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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