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:tgl@xxxxxxxxxxxxx] Sent: March 16, 2012 6:33 AM To: Doug Gorley Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: Zero-length character breaking query? Doug Gorley <dgorley@xxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
