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:t...@sss.pgh.pa.us] Sent: March 16, 2012 6:33 AM To: Doug Gorley Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Zero-length character breaking query? Doug Gorley <dgor...@aihs.ca> 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 (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
