(I first asked for help under postgres-sql, but this looks like it
isn't an sql problem after all.)
I've got a strange problem with wildcards (postgresql 7.0.2).
On some tables, the character combination '.%' in a pattern
prevents the pattern from every matching. But on other tables
the combination works fine. The attached script output shows
the problem. I see this in several different databases, but
so far only in databases with more than a few rows, and with
indices. This is a typical example (I've annotated the output
with a couple of comments, look for lines starting with "> ".
If anyone can explain (a) why I'm seeing this behavior and
(b) what I can do to fix it, I'd be eternally grateful.
Thanks!
--
Steve Wampler- SOLIS Project, National Solar Observatory
swampler@noao.edu> *********************************************************************
> *********** Note: "messages" has 35129 rows. '.%' doesn't work *****
> *********************************************************************
logdb=# \d messages
Table "messages"
Attribute | Type | Modifier
------------+--------------+----------
time_stamp | varchar(32) | not null
category | varchar(32) |
client | varchar(128) |
message | text |
Indices: category_id,
client_id,
time_stamp_id
logdb=# select count(*) from messages where client like 'kp.dhs.v%';
count
-------
2955
(1 row)
logdb=# select count(*) from messages where client like 'kp.dhs.%';
count
-------
0
(1 row)
logdb=# select count(*) from messages where client like 'kp.dhs%';
count
-------
2955
(1 row)
> *********************************************************************
> *********** Note: "messages.2000_195-2000_220" has 28496 rows
> *********** and was created via a "SELECT * INTO TABLE ...."
> *********** from "messages". Note that '.%' works here!
> *********************************************************************
logdb=# \d messages.2000_195-2000_220
Table "messages.2000_195-2000_220"
Attribute | Type | Modifier
------------+--------------+----------
time_stamp | varchar(32) |
category | varchar(32) |
client | varchar(128) |
message | text |
logdb=# select count(*) from "messages.2000_195-2000_220" where client like 'kp. dhs%';
count
-------
17657
(1 row)
logdb=# select count(*) from "messages.2000_195-2000_220" where client like 'kp. dhs.%';
count
-------
17657
(1 row)
logdb=# select count(*) from "messages.2000_195-2000_220" where client like 'kp. dhs.v%';
count
-------
17657
(1 row)
logdb=# \q
