Thanks Tom; this is at a client site, so I have limited access, but it looks like a REINDEX resolves the issue.
Doug -----Original Message----- From: Tom Lane [mailto:[email protected]] Sent: March 16, 2012 6:33 AM To: Doug Gorley Cc: [email protected] Subject: Re: [GENERAL] Zero-length character breaking query? Doug Gorley <[email protected]> writes: > The table is called tdt_unsent. The field is str_name_l. For demonstration > purposes, the value is "SMITH". > "select * from tdt_unsent where str_name_l = 'SMITH'" returns 0 rows. > "select * from tdt_unsent where str_name_l ~ '^SMITH'" returns 3 rows. > "select * from tdt_unsent where str_name_l ~ '^SMITH$'" returns 0 rows. > "select length(str_name_l) from tdt_unsent where str_name_l ~ '^SMITH'" > returns "5". I'd check EXPLAIN (with the actual problematic string, not SMITH). The planner is probably trying to build an index range condition from the regex pattern --- is it doing the right thing given your locale? If the plan looks okay, maybe you need to reindex whatever index it's using. regards, tom lane -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
