pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: weird situation, BUG or I'm not doing it right
5+ messages / 4 participants
[nested] [flat]
* Re: weird situation, BUG or I'm not doing it right
@ 2002-08-25 04:27 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Jiaqing Wang <jjw72@swbell.net>
0 siblings, 2 replies; 5+ messages in thread
From: Ross J. Reedstrom @ 2002-08-25 04:27 UTC (permalink / raw)
To: Jiaqing Wang <jjw72@swbell.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* weird situation, BUG or I'm not doing it right
@ 2002-08-25 05:56 Jiaqing Wang <jjw72@swbell.net>
0 siblings, 1 reply; 5+ messages in thread
From: Jiaqing Wang @ 2002-08-25 05:56 UTC (permalink / raw)
To: pgsql-sql; +Cc: jjw72@swbell.net
Hello,
I found below situation weird, it seems to me a bug.
backend=> select * from valid_addr;
zip_code | city_name | state_abrev
----------+----------------------+-------------
00601 | ADJUNTAS | PR
00602 | AGUADA | PR
00603 | AGUADILLA | PR
00604 | AGUADILLA | PR
00605 | AGUADILLA | PR
00606 | MARICAO | PR
00610 | ANASCO | PR
00611 | ANGELES | PR
00612 | ARECIBO | PR
00613 | ARECIBO | PR
00614 | ARECIBO | PR
00616 | BAJADERO | PR
00617 | BARCELONETA | PR
00622 | BOQUERON | PR
00623 | CABO ROJO | PR
00624 | PENUELAS | PR
00627 | CAMUY | PR
00631 | CASTANER | PR
00636 | ROSARIO | PR
00637 | SABANA GRANDE | PR
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
zip_code | city_name | state_abrev
----------+-------------------+-------------
00601 | ADJUNTAS | PR
00602 | AGUADA | PR
00603 | AGUADILLA | PR
00604 | AGUADILLA | PR
00605 | AGUADILLA | PR
00606 | MARICAO | PR
00610 | ANASCO | PR
00611 | ANGELES | PR
00612 | ARECIBO | PR
00613 | ARECIBO | PR
00614 | ARECIBO | PR
00616 | BAJADERO | PR
00617 | BARCELONETA | PR
00622 | BOQUERON | PR
00623 | CABO ROJO | PR
00624 | PENUELAS | PR
00627 | CAMUY | PR
00631 | CASTANER | PR
00636 | ROSARIO | PR
00637 | SABANA GRANDE | PR
jjw
8/24/2002
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: weird situation, BUG or I'm not doing it right
@ 2002-08-25 12:49 Michael Paesold <mpaesold@gmx.at>
parent: Ross J. Reedstrom <reedstrm@rice.edu>
1 sibling, 1 reply; 5+ messages in thread
From: Michael Paesold @ 2002-08-25 12:49 UTC (permalink / raw)
To: Ross J. Reedstrom <reedstrm@rice.edu>; Jiaqing Wang <jjw72@swbell.net>; +Cc: pgsql-sql
Ross J. Reedstrom wrote:
> 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
Then, why is 'PR' blank padded to char(?) and upper('pr') not?
It seems that when comparing char with text, the comparision is done
as text, not as bpchar.
billing=# select 'A'::char(2) = upper('a');
?column?
----------
f
billing=# select 'A'::char(2) = upper('a')::bpchar;
?column?
----------
t
Regards,
Michael Paesold
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: weird situation, BUG or I'm not doing it right
@ 2002-08-25 15:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Paesold <mpaesold@gmx.at>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2002-08-25 15:20 UTC (permalink / raw)
To: Michael Paesold <mpaesold@gmx.at>; +Cc: Ross J. Reedstrom <reedstrm@rice.edu>; Jiaqing Wang <jjw72@swbell.net>; pgsql-sql
"Michael Paesold" <mpaesold@gmx.at> writes:
> It seems that when comparing char with text, the comparision is done
> as text, not as bpchar.
Yup. Arguably this is a bad idea: the system ought to reject the
comparison entirely, and make you cast one side or the other so that
it's clear to all concerned which comparison semantics you want.
However, I don't see any way to do that without also breaking a lot
of cases that are convenient and don't confuse anyone ... like, say,
the fact that you can apply upper() to char(n) data in the first place.
Upper is declared as "upper(text) returns text".
You might care to read the User's Guide's discussion of type conversion,
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/typeconv.html
The particular behavior at hand emerges from the fact that text is
considered the preferred datatype in the string category.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: weird situation, BUG or I'm not doing it right
@ 2002-08-26 14:56 Jiaqing Wang <jjw72@swbell.net>
parent: Ross J. Reedstrom <reedstrm@rice.edu>
1 sibling, 0 replies; 5+ messages in thread
From: Jiaqing Wang @ 2002-08-26 14:56 UTC (permalink / raw)
To: Ross J. Reedstrom <reedstrm@rice.edu>; +Cc: pgsql-sql
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: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Ross J. Reedstrom
Sent: Saturday, August 24, 2002 9:28 PM
To: Jiaqing Wang
Cc: pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-08-26 14:56 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-25 05:56 weird situation, BUG or I'm not doing it right Jiaqing Wang <jjw72@swbell.net>
2002-08-25 04:27 ` Ross J. Reedstrom <reedstrm@rice.edu>
2002-08-25 12:49 ` Michael Paesold <mpaesold@gmx.at>
2002-08-25 15:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-08-26 14:56 ` Jiaqing Wang <jjw72@swbell.net>
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