From ssutjiono@wc-group.com Fri Feb 22 17:09:05 2002 Received: from qmsql.netservers.net (hpmail1.netservers.net [209.196.128.163]) by postgresql.org (Postfix) with SMTP id 0FBBE476004 for ; Fri, 22 Feb 2002 12:09:05 -0500 (EST) Received: (qmail 68646 invoked from network); 22 Feb 2002 17:09:09 -0000 Received: from unknown (HELO AARON) (66.26.63.73) by hpmail1.netservers.net with SMTP; 22 Feb 2002 17:09:09 -0000 Message-ID: <023101c1bbc3$03a33910$110a010a@headquarters.wcgroup.com> From: "Samuel J. Sutjiono" To: , Subject: Regular Expression for 'and' instead of 'or' Date: Fri, 22 Feb 2002 12:04:38 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_022E_01C1BB99.1A578BE0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200202/1059 X-Sequence-Number: 21056 This is a multi-part message in MIME format. ------=_NextPart_000_022E_01C1BB99.1A578BE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable This expression matches the word socks or shoes or nike in product category= =20 where productdescr ~* '(socks|shoes|nike)' Does anybody know what the expression should be if I want to do 'and' of t= hose key words instead of 'or' ? Thanks, Sam=20 ------=_NextPart_000_022E_01C1BB99.1A578BE0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
This expression matches the word socks or = shoes or=20 nike in product category 
where productdescr ~*=20 '(socks|shoes|nike)'
 
Does anybody know what the expression= should=20 be  if I want to do 'and' of those key words ins= tead=20 of 'or' ?
 
Thanks,
Sam 
------=_NextPart_000_022E_01C1BB99.1A578BE0-- From fschapachnik@vianetworks.com.ar Fri Feb 22 18:15:28 2002 Received: from ns1.via-net-works.net.ar (ns1.via-net-works.net.ar [200.10.100.10]) by postgresql.org (Postfix) with ESMTP id D6334475E72; Fri, 22 Feb 2002 13:15:28 -0500 (EST) Received: (from fpscha@localhost) by ns1.via-net-works.net.ar (8.9.3/8.9.3) id PAA49730; Fri, 22 Feb 2002 15:15:14 -0300 (ART) X-Authentication-Warning: ns1.via-net-works.net.ar: fpscha set sender to fschapachnik@vianetworks.com.ar using -f Date: Fri, 22 Feb 2002 15:15:14 -0300 From: Fernando Schapachnik To: "Samuel J. Sutjiono" Cc: pgsql-sql@postgresql.org, pgsql-general@postgresql.org Subject: Re: Regular Expression for 'and' instead of 'or' Message-ID: <20020222151514.E2066@ns1.via-net-works.net.ar> References: <023101c1bbc3$03a33910$110a010a@headquarters.wcgroup.com> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit User-Agent: Mutt/1.2.5i In-Reply-To: <023101c1bbc3$03a33910$110a010a@headquarters.wcgroup.com>; from ssutjiono@wc-group.com on Fri, Feb 22, 2002 at 12:04:38PM -0500 X-Archive-Number: 200202/1067 X-Sequence-Number: 21064 En un mensaje anterior, Samuel J. Sutjiono escribió: > This expression matches the word socks or shoes or nike in product category > where productdescr ~* '(socks|shoes|nike)' > > Does anybody know what the expression should be if I want to do 'and' of those key words instead of 'or' ? You have to use something like: '(socks.*shoes.*nike)|(socks.*nike.*shoes)|...' where ... is every other possible combination. Not pretty, but that's a limitation of finite automata (or regular expressions, which are the same). Regards. Fernando P. Schapachnik Gerente de tecnología de red y sistemas de información VIA NET.WORKS ARGENTINA S.A. fschapachnik@vianetworks.com.ar Tel.: (54-11) 4323-3381 From ssutjiono@wc-group.com Fri Feb 22 18:43:15 2002 Received: from qmsql.netservers.net (hpmail1.netservers.net [209.196.128.163]) by postgresql.org (Postfix) with SMTP id 2221747630C for ; Fri, 22 Feb 2002 13:43:15 -0500 (EST) Received: (qmail 50472 invoked from network); 22 Feb 2002 18:38:35 -0000 Received: from unknown (HELO AARON) (66.26.63.73) by hpmail1.netservers.net with SMTP; 22 Feb 2002 18:38:35 -0000 Message-ID: <025b01c1bbcf$81dccc40$110a010a@headquarters.wcgroup.com> From: "Samuel J. Sutjiono" To: "Fernando Schapachnik" Cc: , References: <023101c1bbc3$03a33910$110a010a@headquarters.wcgroup.com> <20020222151514.E2066@ns1.via-net-works.net.ar> Subject: Re: [SQL] Regular Expression for 'and' instead of 'or' Date: Fri, 22 Feb 2002 13:34:04 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200202/1072 X-Sequence-Number: 21069 Thank you very much Fernando. Do you know whether this expression (regex) use index scan ? Regards, Sam ----- Original Message ----- From: "Fernando Schapachnik" To: "Samuel J. Sutjiono" Cc: ; Sent: Friday, February 22, 2002 1:15 PM Subject: Re: [SQL] [GENERAL] Regular Expression for 'and' instead of 'or' > En un mensaje anterior, Samuel J. Sutjiono escribió: > > This expression matches the word socks or shoes or nike in product category > > where productdescr ~* '(socks|shoes|nike)' > > > > Does anybody know what the expression should be if I want to do 'and' of those key words instead of 'or' ? > > You have to use something like: > > '(socks.*shoes.*nike)|(socks.*nike.*shoes)|...' > > where ... is every other possible combination. Not pretty, but that's > a limitation of finite automata (or regular expressions, which are > the same). > > Regards. > > > > Fernando P. Schapachnik > Gerente de tecnología de red > y sistemas de información > VIA NET.WORKS ARGENTINA S.A. > fschapachnik@vianetworks.com.ar > Tel.: (54-11) 4323-3381 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > From fschapachnik@vianetworks.com.ar Fri Feb 22 18:46:53 2002 Received: from ns1.via-net-works.net.ar (ns1.via-net-works.net.ar [200.10.100.10]) by postgresql.org (Postfix) with ESMTP id 81B35476629; Fri, 22 Feb 2002 13:46:53 -0500 (EST) Received: (from fpscha@localhost) by ns1.via-net-works.net.ar (8.9.3/8.9.3) id PAA81259; Fri, 22 Feb 2002 15:46:48 -0300 (ART) X-Authentication-Warning: ns1.via-net-works.net.ar: fpscha set sender to fschapachnik@vianetworks.com.ar using -f Date: Fri, 22 Feb 2002 15:46:48 -0300 From: Fernando Schapachnik To: "Samuel J. Sutjiono" Cc: pgsql-sql@postgresql.org, pgsql-general@postgresql.org Subject: Re: [SQL] Regular Expression for 'and' instead of 'or' Message-ID: <20020222154648.F2066@ns1.via-net-works.net.ar> References: <023101c1bbc3$03a33910$110a010a@headquarters.wcgroup.com> <20020222151514.E2066@ns1.via-net-works.net.ar> <025b01c1bbcf$81dccc40$110a010a@headquarters.wcgroup.com> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit User-Agent: Mutt/1.2.5i In-Reply-To: <025b01c1bbcf$81dccc40$110a010a@headquarters.wcgroup.com>; from ssutjiono@wc-group.com on Fri, Feb 22, 2002 at 01:34:04PM -0500 X-Archive-Number: 200202/1071 X-Sequence-Number: 21068 En un mensaje anterior, Samuel J. Sutjiono escribió: > Thank you very much Fernando. Do you know whether this expression (regex) > use index scan ? Not sure, but probably an EXPLAIN will tell. Good luck. Fernando P. Schapachnik Gerente de tecnología de red y sistemas de información VIA NET.WORKS ARGENTINA S.A. fschapachnik@vianetworks.com.ar Tel.: (54-11) 4323-3381 From tgl@sss.pgh.pa.us Fri Feb 22 20:20:49 2002 Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id B825B476049; Fri, 22 Feb 2002 15:20:49 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g1MKKdS19466; Fri, 22 Feb 2002 15:20:39 -0500 (EST) To: Fernando Schapachnik Cc: "Samuel J. Sutjiono" , pgsql-sql@postgresql.org, pgsql-general@postgresql.org Subject: Re: Regular Expression for 'and' instead of 'or' In-reply-to: <20020222151514.E2066@ns1.via-net-works.net.ar> References: <023101c1bbc3$03a33910$110a010a@headquarters.wcgroup.com> <20020222151514.E2066@ns1.via-net-works.net.ar> Comments: In-reply-to Fernando Schapachnik message dated "Fri, 22 Feb 2002 15:15:14 -0300" Date: Fri, 22 Feb 2002 15:20:39 -0500 Message-ID: <19463.1014409239@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200202/1078 X-Sequence-Number: 21074 Fernando Schapachnik writes: > You have to use something like: > '(socks.*shoes.*nike)|(socks.*nike.*shoes)|...' > where ... is every other possible combination. Not pretty, but that's > a limitation of finite automata (or regular expressions, which are > the same). Seems a lot easier to do WHERE field ~* 'socks' AND field ~* 'shoes' AND field ~* 'nike' regards, tom lane From peter_e@gmx.net Fri Feb 22 20:42:10 2002 Received: from student.gvsu.edu (unknown [148.61.7.124]) by postgresql.org (Postfix) with ESMTP id BBDA0475BE7; Fri, 22 Feb 2002 15:42:10 -0500 (EST) Received: from [148.61.250.127] [148.61.250.127] by student.gvsu.edu with Novonyx SMTP Server $Revision: 2.74 $; Fri, 22 Feb 2002 15:42:13 -0500 (EDT) Date: Fri, 22 Feb 2002 15:45:07 -0500 (EST) From: Peter Eisentraut X-Sender: To: "Samuel J. Sutjiono" Cc: , Subject: Re: [SQL] Regular Expression for 'and' instead of 'or' In-Reply-To: <023101c1bbc3$03a33910$110a010a@headquarters.wcgroup.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200202/1081 X-Sequence-Number: 21077 Samuel J. Sutjiono writes: > This expression matches the word socks or shoes or nike in product category > where productdescr ~* '(socks|shoes|nike)' > > Does anybody know what the expression should be if I want to do 'and' > of those key words instead of 'or' ? You probably want something like productdescr ~* 'socks' AND productdescr ~* 'shoes' AND productdescr ~* 'nike' I don't think it's possible to do this for the general case with just a regular expression. -- Peter Eisentraut peter_e@gmx.net From medi@cybershell.com Fri Feb 22 20:24:33 2002 Received: from mail2.cybershell.com (unknown [63.221.64.67]) by postgresql.org (Postfix) with SMTP id 13CE3476730 for ; Fri, 22 Feb 2002 15:24:33 -0500 (EST) Received: (qmail 26784 invoked from network); 22 Feb 2002 22:18:06 -0000 Received: from unknown (HELO cybershell.com) (@63.221.64.66) by mail2.cybershell.com with SMTP; 22 Feb 2002 22:18:06 -0000 Message-ID: <3C76B794.445B2D71@cybershell.com> Date: Fri, 22 Feb 2002 13:26:44 -0800 From: Medi Montaseri X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.4.2-2 i686) X-Accept-Language: en MIME-Version: 1.0 To: "Samuel J. Sutjiono" Cc: pgsql-sql@postgresql.org, pgsql-general@postgresql.org Subject: Re: Regular Expression for 'and' instead of 'or' References: <023101c1bbc3$03a33910$110a010a@headquarters.wcgroup.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200202/1080 X-Sequence-Number: 21076 Based on generic rules of Regular Expression, the default is a AND. For example RE-1RE-2RE-3 is really (RE-1) and (RE-2) and (RE-3). However Regular Expression also provides the "Alternation Metachar" to change it from a default AND to a OR. The problem however is if you have target-1 junk target-2 junk junk target-3 Then the simple (target-1target-2target-3) would not work, as you are implying they are following each other. So the fix could be productdescr ~* '(socks)' AND productdescr ~* '(shoes)' AND productdescr ~*(nike)' Effectively you are creating a composite expression consisting of three sub-expression. "Samuel J. Sutjiono" wrote: > This expression matches the word socks or shoes or nike in product > categorywhere productdescr ~* '(socks|shoes|nike)' Does anybody know > what the expression should be if I want to do 'and' of those key > words instead of 'or' ? Thanks,Sam -- ------------------------------------------------------------------------- Medi Montaseri medi@CyberShell.com Unix Distributed Systems Engineer HTTP://www.CyberShell.com CyberShell Engineering ------------------------------------------------------------------------- From mallah@trade-india.com Sat Feb 23 06:22:24 2002 Received: from trade-india.com (unknown [130.94.20.28]) by postgresql.org (Postfix) with SMTP id 33FA0475ACA for ; Sat, 23 Feb 2002 01:22:24 -0500 (EST) Received: (qmail 11037 invoked from network); 23 Feb 2002 06:27:07 -0000 Received: from ptil-235-129-del.primus-india.net (HELO trade-india.com) (203.196.129.235) by 130.94.22.209 with SMTP; 23 Feb 2002 06:27:07 -0000 Message-ID: <3C7735D6.7E87781A@trade-india.com> Date: Sat, 23 Feb 2002 11:55:26 +0530 From: Rajesh Kumar Mallah X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.4.2-2 i686) X-Accept-Language: en MIME-Version: 1.0 To: "Samuel J. Sutjiono" , pgsql-sql@postgresql.org Subject: Re: Regular Expression for 'and' instead of 'or' References: <023101c1bbc3$03a33910$110a010a@headquarters.wcgroup.com> Content-Type: multipart/alternative; boundary="------------12F2472176ED16E07558E0C0" X-Archive-Number: 200202/335 X-Sequence-Number: 6757 --------------12F2472176ED16E07558E0C0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit How about using the contrib/tsearch module that uses gist indexes. moreover it has builtin support for word stemming,morphology and stuff. (to my knowledge) you would ofcourse require to upgrade to PGSQL7.2 excrepts from README.tsearch contrib module. ================ and now you can search all titles with words 'patch' and 'gist': select title from titles where titleidx ## 'patch&gist'; Here, ## is a new operation defined for type 'txtidx' which could use index (if exists) built on titleidx. This operator uses morphology to expand query, i.e. ## 'patches&gist' will find titles with 'patch' and 'gist' also. If you want to provide query as is, use operator @@ instead: select title from titles where titleidx @@ 'patch&gist'; "Samuel J. Sutjiono" wrote: > This expression matches the word socks or shoes or nike in product > categorywhere productdescr ~* '(socks|shoes|nike)' Does anybody know > what the expression should be if I want to do 'and' of those key > words instead of 'or' ? Thanks,Sam --------------12F2472176ED16E07558E0C0 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit How about  using the  contrib/tsearch  module
that uses gist indexes.

moreover it has  builtin support for word stemming,morphology
and stuff. (to my knowledge)

you would ofcourse require to upgrade to PGSQL7.2
 

excrepts from README.tsearch contrib module.
================
and now you can search all titles with words 'patch' and 'gist':
  select title from titles where titleidx ## 'patch&gist';

Here, ## is a new operation defined for type 'txtidx' which could use index
(if exists) built on titleidx. This operator uses morphology to
expand query, i.e.
  ## 'patches&gist' will find titles with 'patch' and 'gist' also.
If you want to provide query as is, use operator @@ instead:
  select title from titles where titleidx @@ 'patch&gist';
 
 

"Samuel J. Sutjiono" wrote:

This expression matches the word socks or shoes or nike in product categorywhere productdescr ~* '(socks|shoes|nike)' Does anybody know what the expression should be  if I want to do 'and' of those key words instead of 'or' ? Thanks,Sam
--------------12F2472176ED16E07558E0C0--