Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e7P4NI596158 for ; Fri, 25 Aug 2000 00:23:20 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id AAA00828; Fri, 25 Aug 2000 00:22:35 -0400 (EDT) To: rodgerd@diaspora.gen.nz cc: pgsql-sql@postgresql.org Subject: Re: Regular expression query In-reply-to: <20000825151333.A26833@diaspora.gen.nz> References: <20000825151333.A26833@diaspora.gen.nz> Comments: In-reply-to Rodger Donaldson message dated "Fri, 25 Aug 2000 15:13:34 +1200" Date: Fri, 25 Aug 2000 00:22:35 -0400 Message-ID: <825.967177355@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200008/274 Rodger Donaldson writes: > SELECT url > FROM sites > WHERE url ~ url || '\\s+' > While this concatenation works with the LIKE directive (ie LIKE url || '%'), > postgresql barfs on it in a regexp with the error: > ERROR: Unable to identify an operator '||' for types 'bool' and 'unknown' > You will have to retype this query using an explicit cast LIKE and ~ do not have the same precedence. See http://www.postgresql.org/docs/postgres/operators.htm. ~ and || actually fall in the same category ("all other") and therefore are grouped left-to-right; so you're getting (url ~ url) || '...'. > The other aspect of this is that it seems that postgresql's regexp engine > doesn't understand some expected regexps; I've tried both escaped and > unescaped versions of, eg \w, \s, \n and so on a pg seems to ignore them. The regexp package we currently use implements POSIX 1003.2 regexps (see src/backend/regex/re_format.7). I believe there is an item on the TODO list about upgrading the regexp parser to something more modern ... feel free to hop on that project if it's bugging you ... regards, tom lane