pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect * from users WHERE upper(lastName) = upper('Pringle')
3+ messages / 3 participants
[nested] [flat]
* Select * from users WHERE upper(lastName) = upper('Pringle')
@ 2003-01-10 23:45 Hale Pringle <halepringle@yahoo.com>
2003-01-12 04:45 ` Re: Select * from users WHERE upper(lastName) = upper('Pringle') Tom Lane <tgl@sss.pgh.pa.us>
2003-01-14 16:34 ` Re: Select * from users WHERE upper(lastName) = upper('Pringle') Andrew Sullivan <andrew@libertyrms.info>
0 siblings, 2 replies; 3+ messages in thread
From: Hale Pringle @ 2003-01-10 23:45 UTC (permalink / raw)
To: pgsql-jdbc@postgresql.org
I have notices that when you want to use the upper() function, you must also
use the trim().
For example,
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.
This seems odd to me. Can someone explain?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Select * from users WHERE upper(lastName) = upper('Pringle')
2003-01-10 23:45 Select * from users WHERE upper(lastName) = upper('Pringle') Hale Pringle <halepringle@yahoo.com>
@ 2003-01-12 04:45 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-01-12 04:45 UTC (permalink / raw)
To: Hale@halepringle.com; +Cc: pgsql-jdbc@postgresql.org; pgsql-sql
"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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Select * from users WHERE upper(lastName) = upper('Pringle')
2003-01-10 23:45 Select * from users WHERE upper(lastName) = upper('Pringle') Hale Pringle <halepringle@yahoo.com>
@ 2003-01-14 16:34 ` Andrew Sullivan <andrew@libertyrms.info>
1 sibling, 0 replies; 3+ messages in thread
From: Andrew Sullivan @ 2003-01-14 16:34 UTC (permalink / raw)
To: pgsql-jdbc@postgresql.org
On Fri, Jan 10, 2003 at 06:45:43PM -0500, Hale Pringle wrote:
> I have notices that when you want to use the upper() function, you must also
> use the trim().
No. But. . .
> For example,
> 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.
>
> This seems odd to me. Can someone explain?
. . .yes. The problem has to so with the char() data type, which is
padded per spec. So if you do anything on it, you need to trim() it.
I believe there is a special case in place in the spec just for the
SELECTs, but someone more familiar with the specification can help.
If you don't really need exactly 40 characters, use varchar() or text().
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-01-14 16:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-10 23:45 Select * from users WHERE upper(lastName) = upper('Pringle') Hale Pringle <halepringle@yahoo.com>
2003-01-12 04:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-14 16:34 ` Andrew Sullivan <andrew@libertyrms.info>
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