pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Regular expression query
4+ messages / 4 participants
[nested] [flat]

* Regular expression query
@ 2000-08-25 03:13 Rodger Donaldson <rodgerd@diaspora.gen.nz>
  2000-08-25 04:22 ` Re: Regular expression query Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Rodger Donaldson @ 2000-08-25 03:13 UTC (permalink / raw)
  To: pgsql-sql


I have a large group of records which were entered with trailing garbage, in
the form of superfluous \n.

The main problem this has caused, other than the obvious one, is that the
record in question is being used as a primary key, so some duplicates have
slipped through.  I assumed a simple statement like:

SELECT url 
FROM sites
WHERE url ~ url || '\\s+'

...would allow me to find all the duplicate-but-not-quite records.

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

Encapsulating the concatenation in brackets leads the query parser to stop
bleating.  I presume this is simply a limitation in the parser's ability to
make inferences about regexps vs. LIKEs.

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.
Am I exceeding the capabilities of the regexp parser?

-- 
Rodger Donaldson		rodgerd@diaspora.gen.nz
I just had this vision of a young boy cowering in terror, whispering:
"I see dumb people"
		-- Steve VanDevender 



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Regular expression query
  2000-08-25 03:13 Regular expression query Rodger Donaldson <rodgerd@diaspora.gen.nz>
@ 2000-08-25 04:22 ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-08-25 06:43   ` Re: Regular expression query Oliver Seidel <os10000@in-medias-res.com>
  2000-10-15 18:50   ` Re: Regular expression query Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 2 replies; 4+ messages in thread

From: Tom Lane @ 2000-08-25 04:22 UTC (permalink / raw)
  To: rodgerd@diaspora.gen.nz; +Cc: pgsql-sql

Rodger Donaldson <rodgerd@diaspora.gen.nz> 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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Regular expression query
  2000-08-25 03:13 Regular expression query Rodger Donaldson <rodgerd@diaspora.gen.nz>
  2000-08-25 04:22 ` Re: Regular expression query Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-08-25 06:43   ` Oliver Seidel <os10000@in-medias-res.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Oliver Seidel @ 2000-08-25 06:43 UTC (permalink / raw)
  To: Rodger Donaldson <rodgerd@diaspora.gen.nz>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@hub.org

> 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 ...

I would like to recommend

ftp://ftp.cus.cam.ac.uk/pub/software/programs/pcre/ 

which stands for "PERL compatible regular expressions" and has an
expression analyzer & compiler for more efficient repeated matching.
Since Philip Hazel has only written this package to support his Mail
Transfer Agent (exim), it is optimised for performance.

I hope I have been of help,

Oliver Seidel




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Regular expression query
  2000-08-25 03:13 Regular expression query Rodger Donaldson <rodgerd@diaspora.gen.nz>
  2000-08-25 04:22 ` Re: Regular expression query Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-10-15 18:50   ` Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 0 replies; 4+ messages in thread

From: Bruce Momjian @ 2000-10-15 18:50 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: rodgerd@diaspora.gen.nz; pgsql-sql

> Rodger Donaldson <rodgerd@diaspora.gen.nz> 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 ...

I bug Henry Spencer regularly.  His new code is in TCL/TK, but has not
been released into any other code.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2000-10-15 18:50 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-25 03:13 Regular expression query Rodger Donaldson <rodgerd@diaspora.gen.nz>
2000-08-25 04:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-08-25 06:43   ` Oliver Seidel <os10000@in-medias-res.com>
2000-10-15 18:50   ` Bruce Momjian <pgman@candle.pha.pa.us>

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