agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Campbell\, Lance <lance@illinois.edu>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Help with a not match
Date: Fri, 09 Nov 2018 22:04:32 +0000
Message-ID: <87a7mix73u.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <F155F457-310F-41FC-B3F5-9544D22A5C77@illinois.edu>
References: <E48BCA4C-5847-4634-8E9D-A91C9C4E40E6@illinois.edu>
	<CAKFQuwaSZyaiKpo1wfWWnuwfdYvqnM3ePjNxPXBt+o5TNUHEFA@mail.gmail.com>
	<47589FBF-7910-4F95-8239-BDF702A19B2C@illinois.edu>
	<CAKFQuwZntFfvhrobCSwPV1jN=JjkGLRRKsy317dHu4B+4FKqCQ@mail.gmail.com>
	<F155F457-310F-41FC-B3F5-9544D22A5C77@illinois.edu>

>>>>> "Campbell" == Campbell, Lance <lance@illinois.edu> writes:

 Campbell> Very helpful.  I am almost there.  
 Campbell> I created this SQL:

 Campbell> SELECT regexp_matches(content, '/(?!files/'||id||'/)(files/\d+/)/', 'g') FROM tablea

 Campbell> I get no matches. My guess is I am close but slightly off on
 Campbell> the syntax.

Simplest regexp solution is to do this:

SELECT ... WHERE content ~ ('files/(?!' ||id|| '/)\d+/')

i.e. we're generating a regexp like 'files/(?!123/)\d+/' for each row.
No need for regexp_matches in this case because all we're looking for is
whether a match exists.

Another, possibly faster because it doesn't need a regexp compile for
each row, but possibly slower due to subplan overhead, would be:

SELECT ...
 WHERE id::text <> ANY (SELECT (regexp_matches(content, 'files/(\d+)/', 'g'))[1])

The idea of the second method is to extract all the "NNN" values from
files/NNN/ substrings, and then test whether any NNN value is different
from the expected one. (This is a VERY RARE use of "<> ANY"; normally
one uses "<> ALL" as the negation of "= ANY", but the logic here
requires the negation of "= ALL" instead.)

-- 
Andrew (irc:RhodiumToad)

view thread (7+ messages)

Message-ID: <87a7mix73u.fsf@news-spur.riddles.org.uk>
Permalink:  ../87a7mix73u.fsf@news-spur.riddles.org.uk/
Also on:    postgresql.org/message-id/87a7mix73u.fsf@news-spur.riddles.org.uk

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: andrew@tao11.riddles.org.uk, lance@illinois.edu, david.g.johnston@gmail.com
  Subject: Re: Help with a not match
  In-Reply-To: <87a7mix73u.fsf@news-spur.riddles.org.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox