Обсуждение: Where clause...

Hello,

I have a table which a column "fee_type " declared as char(1)

The column can contain 'I' (uppercase i), 'C' or 'R'

When querying with Select * from <table> where fee_type in ('I', 'C')

the query returns nothing.

The query returns the rows I am expecting when doing one of the following:

where fee_type ilike ('I') or fee_type ilike ('C')
or
where fe.fee_type = chr(73) or fee_type = chr(67)
(this proves the value is indeed uppercase)

but nothing is returned either when I try
where fee_type like ('I') or fee_type like ('C')

I thought it would be related to the datatype so I tried casting the
'I' and 'C' to char(1), but I had no luck.

Could you give me a hint ?

thanks
Didier

am  Sun, dem 03.02.2008, um  9:26:34 +0100 mailte Didier Gasser-Morlay folgendes:
> Hello,
>
> I have a table which a column "fee_type " declared as char(1)
>
> The column can contain 'I' (uppercase i), 'C' or 'R'
>
> When querying with Select * from <table> where fee_type in ('I', 'C')
>
> the query returns nothing.

Works for me:

test=*# select * from fee;
 id | fee_type
----+----------
  1 | C
  2 | I
  3 | c
  4 | i
  5 | a
(5 rows)

test=*# select * from fee where fee_type in ('C','I');
 id | fee_type
----+----------
  1 | C
  2 | I
(2 rows)


Please show us your table description (\d table like
test=*# \d fee
         Table "public.fee"
  Column  |     Type     | Modifiers
----------+--------------+-----------
 id       | integer      |
 fee_type | character(1) |


and tell us your PG-Version.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Andreas,

Thanks for asking about the PG version .... this server was still
running 8.3 BETA2, I now upgraded to RC2 and it works as I expected.
ie the in ('C', 'I') returns rows!

Didier

Didier Gasser-Morlay <didiergm@gmail.com> schrieb:

> Andreas,
>
> Thanks for asking about the PG version .... this server was still
> running 8.3 BETA2, I now upgraded to RC2 and it works as I expected.
> ie the in ('C', 'I') returns rows!

What, in the 8.3 BETA2 is a such big mistake?

But okay, your problem is solved.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Andreas,

I cannot guarantee it was a real 8.3 BETA2 issue, it could have been
something else, because a collegue of mine, still running BETA2 could
run that query fine.

I might have had a corrupted table. But since installing RC2 had me
reload the backup ... I cannot be sure.

I would hate to blame the PostgreSQL dev team for something else.

Didier