Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id 3150F9F9F44 for ; Fri, 10 Nov 2006 13:27:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.60]) (amavisd-new, port 10024) with ESMTP id 09392-09 for ; Fri, 10 Nov 2006 13:27:04 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web31808.mail.mud.yahoo.com (web31808.mail.mud.yahoo.com [68.142.207.71]) by postgresql.org (Postfix) with SMTP id 0EDE59F9EED for ; Fri, 10 Nov 2006 13:27:03 -0400 (AST) Received: (qmail 48458 invoked by uid 60001); 10 Nov 2006 17:27:03 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=IylupRinJ3Q3lY5ZM3DAKFuKQ8yqAEiWaUbjWItIRSDf1qjZmq7KWIgPdqrA8+tsU72FjzKy7eOXmlHeEwWDF+duu934ExQha96o304uv8+ZUwvl77Ih3/MZ4U6W6CLAA4jBZc6Xt90mW2kAJXmNX3sZdm+fQORe+GvNi66c6ik= ; X-YMail-OSG: hC7mIUoVM1mJ6yPO7jf3EE2LEcsmEQ2kRBA24O0e4hwzX4hLc9lc4_SyZ0kyiokFTA5Ea.tvN5P36wnrovsxPb0IjQDQZA_1QMNTVVoqLlSvv728B4ciYk.GgVGlpU6B4uJdl62cGI76KUVn9YeJD0Wubv8Cf33quNQyK1voWOJvkJVRl2W8Hn9i2J7W Received: from [12.177.97.50] by web31808.mail.mud.yahoo.com via HTTP; Fri, 10 Nov 2006 09:27:02 PST Date: Fri, 10 Nov 2006 09:27:02 -0800 (PST) From: Richard Broersma Jr Subject: Re: PostgesSQL equivalent of the Oracle 'contains' operator To: stafford@marine.rutgers.edu, pgsql-sql@postgresql.org In-Reply-To: <4554A46E.5020201@marine.rutgers.edu> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Message-ID: <686466.47178.qm@web31808.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.333 tagged_above=0 required=5 tests=AWL, BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS X-Spam-Level: X-Archive-Number: 200611/116 X-Sequence-Number: 26768 > I'm beginning a port of an Oracle application to PostgrSQL. One > immediate issue is the use of the Oracle 'contains' operator to do > whole word searches on table rows that contain blank separated lists of > names. I was told that the original application was written using the > 'like' operator but the contains operator was found to be much faster. > > Is there a PostgreSQL equivalent for this type of use of the Oracle > contains? The other methods are to use regular expressions, but I do not expect them to be any faster than a like. Have you looked at the contrib module tsearch2? http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ Regards, Richard Broersma Jr.