Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 77DE79FB1CA for ; Sun, 22 Jul 2007 01:41:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 12437-06 for ; Sun, 22 Jul 2007 01:41:40 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by postgresql.org (Postfix) with ESMTP id 634C09FB1BA for ; Sun, 22 Jul 2007 01:41:39 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.8/8.13.8) with ESMTP id l6M4fZAR055715 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Sat, 21 Jul 2007 22:41:37 -0600 (MDT) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.14.1/8.14.1) with ESMTP id l6M4fY0f055806; Sat, 21 Jul 2007 22:41:34 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.14.1/8.14.1/Submit) id l6M4fYwU055805; Sat, 21 Jul 2007 22:41:34 -0600 (MDT) (envelope-from mfuhr) Date: Sat, 21 Jul 2007 22:41:34 -0600 From: Michael Fuhr To: Jessica Richard Cc: pgsql-admin@postgresql.org Subject: Re: "_" in a serach pattern Message-ID: <20070722044134.GA55486@winnie.fuhr.org> References: <102980.5844.qm@web56412.mail.re3.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <102980.5844.qm@web56412.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/241 X-Sequence-Number: 26222 On Fri, Jul 20, 2007 at 09:06:53AM -0700, Jessica Richard wrote: > I have a huge table that has a char(80) name column (indexed). Do you really nead char(N) instead of varchar(N) or text? > The name pattern is like > > A_B_C-D.123.456.pdf > A_B_C-D.123.333.doc.2 > > When I select name from table where name like 'A_B%', it was doing a > table scan; What does EXPLAIN ANALYZE show? When you created the database, what locale did you use? What do "show lc_ctype" and "show server_encoding" return? > I guess the underscore "_" was treated like a special character, > instead of a character "_". Yes -- see the Pattern Matching section of the documentation: http://www.postgresql.org/docs/8.2/interactive/functions-matching.html#FUNCTIONS-LIKE > When I use select name from table where name like 'A\\_B%', it was doing > an index scan with my result returned very fast, What does EXPLAIN ANALYZE show for this query? > but I got a warning about the \\: > > WARNING: nonstandard use of \\ in a string literal > LINE 1: ...elect name from table where file_name like 'A\\_B... > ^ > HINT: Use the escape string syntax for backslashes, e.g., E'\\'. As the hint suggests, you can avoid the warning by using E'A\\_B%'. > By the way, the wild card works if it is at the end of the query, it > does not seem to work if it is in the middle: > > select name from table where name like 'A\\_B%' -- fine; > > select name from table were name like 'A\\_B%.pdf'; -- returns nothing. This is probably due to the column type being char(N) instead of varchar(N) or text: test=> select 'abc'::char(3) like 'a%c'; ?column? ---------- t (1 row) test=> select 'abc'::char(4) like 'a%c'; ?column? ---------- f (1 row) test=> select 'abc'::char(4) like 'a%c%'; ?column? ---------- t (1 row) test=> select 'abc'::char(4) like 'a%c '; ?column? ---------- t (1 row) I had thought that char(N)'s padding spaces were supposed to be semantically insignificant but I don't know if that applies to pattern matching. > questions: > > 1. How do I get rid of the nonstandard warning, but still using the index search? Do what the HINT suggests: use E'pattern' instead of 'pattern', or use dollar quotes. http://www.postgresql.org/docs/8.2/interactive/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING If you're using a non-C locale then you'll need to create an index using one of the pattern_ops operator classes to get pattern matches to use an index. http://www.postgresql.org/docs/8.2/interactive/indexes-opclass.html > 2. How do I search with a wild card % in the middle of the pattern? Would > varchar(80) or char(80) make a difference about the wild card search > (% in the middle)? See the above examples. I'd recommend using varchar(N) or text unless you have a good reason to use char(N). -- Michael Fuhr