Help with problem using wildcard in string value expression
От | Steve Wampler |
---|---|
Тема | Help with problem using wildcard in string value expression |
Дата | |
Msg-id | 39A5AB83.4B6EA4AF@noao.edu обсуждение исходный текст |
Список | pgsql-general |
(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
В списке pgsql-general по дате отправления: