Postgres Pro
Downloads
Home   >   mailing lists

Thread: POSIX RE starting with a (

POSIX RE starting with a (

From
Nick Barr
Date:
Hi,

Just noticed something funny with the POSIX Regular expressions and
wondered if it is a bug, or whether I am doing something wrong. I am
using the POSIX RE in a function that is used in several places so would
have to put some nasty if statement in there for this side case if there
is not work around.

======================================================================
intranet=# select track_id, track_name from ms_track where track_name
like '(%';
  track_id |           track_name
----------+---------------------------------
      1294 | (I Can''t Get No) Satisfaction
      1340 | (Hidden Track)
      1503 | (Nice Dream)
      1942 | (I) Get Lost
(4 rows)

intranet=# select track_name from ms_track where track_name ~ '^\(';
ERROR:  invalid regular expression: parentheses () not balanced
intranet=# select track_name from ms_track where track_name ~ '^(';
ERROR:  invalid regular expression: parentheses () not balanced
intranet=# select track_name from ms_track where track_name ~ '^\(';
ERROR:  invalid regular expression: parentheses () not balanced
======================================================================

Now I have tried a similar query using the PHP POSIX Regular Expressions
and it accepts the sequence '^\(' and matches correctly. Is this a
"feature" of PG that cannot be worked around easily?

Any thoughts?

Thanks

Nick

P.S. Thanks a lot guys for all the hard work on 8.0, looks good to me.
Lots of really useful features, PITR, Win32, Nested transactions. Good
work guys!

Re: POSIX RE starting with a (

From
Nick Barr
Date:
Nick Barr wrote:

> Hi,
>
> Just noticed something funny with the POSIX Regular expressions and
> wondered if it is a bug, or whether I am doing something wrong. I am
> using the POSIX RE in a function that is used in several places so would
> have to put some nasty if statement in there for this side case if there
> is not work around.
>
> ======================================================================
> intranet=# select track_id, track_name from ms_track where track_name
> like '(%';
>  track_id |           track_name
> ----------+---------------------------------
>      1294 | (I Can''t Get No) Satisfaction
>      1340 | (Hidden Track)
>      1503 | (Nice Dream)
>      1942 | (I) Get Lost
> (4 rows)
>
> intranet=# select track_name from ms_track where track_name ~ '^\(';
> ERROR:  invalid regular expression: parentheses () not balanced
> intranet=# select track_name from ms_track where track_name ~ '^(';
> ERROR:  invalid regular expression: parentheses () not balanced
> intranet=# select track_name from ms_track where track_name ~ '^\(';
> ERROR:  invalid regular expression: parentheses () not balanced
> ======================================================================
>
> Now I have tried a similar query using the PHP POSIX Regular Expressions
> and it accepts the sequence '^\(' and matches correctly. Is this a
> "feature" of PG that cannot be worked around easily?
>
> Any thoughts?
>
> Thanks
>
> Nick
>
> P.S. Thanks a lot guys for all the hard work on 8.0, looks good to me.
> Lots of really useful features, PITR, Win32, Nested transactions. Good
> work guys!

Sorry should of said I am running PG 7.4.3.


Nick

Re: POSIX RE starting with a (

From
Oliver Elphick
Date:
On Wed, 2004-08-11 at 14:39, Nick Barr wrote:
> Hi,
>
> Just noticed something funny with the POSIX Regular expressions and
> wondered if it is a bug, or whether I am doing something wrong. I am
> using the POSIX RE in a function that is used in several places so would
> have to put some nasty if statement in there for this side case if there
> is not work around.
>
> ======================================================================
> intranet=# select track_id, track_name from ms_track where track_name
> like '(%';
>   track_id |           track_name
> ----------+---------------------------------
>       1294 | (I Can''t Get No) Satisfaction
>       1340 | (Hidden Track)
>       1503 | (Nice Dream)
>       1942 | (I) Get Lost
> (4 rows)
>
> intranet=# select track_name from ms_track where track_name ~ '^\(';
> ERROR:  invalid regular expression: parentheses () not balanced
> intranet=# select track_name from ms_track where track_name ~ '^(';
> ERROR:  invalid regular expression: parentheses () not balanced
> intranet=# select track_name from ms_track where track_name ~ '^\(';
> ERROR:  invalid regular expression: parentheses () not balanced
> ======================================================================
>
> Now I have tried a similar query using the PHP POSIX Regular Expressions
> and it accepts the sequence '^\(' and matches correctly. Is this a
> "feature" of PG that cannot be worked around easily?

You need to escape the backslash:

  select track_name from ms_track where track_name ~ '^\\
(';
--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "Be still before the LORD and wait patiently for him;
      do not fret when men succeed in their ways, when they
      carry out their wicked schemes."
                            Psalms 37:7

Re: POSIX RE starting with a (

From
Tom Lane
Date:
Nick Barr <nicky@chuckie.co.uk> writes:
> intranet=# select track_name from ms_track where track_name ~ '^\(';
> ERROR:  invalid regular expression: parentheses () not balanced

You've forgotten that the string-literal parser will eat one level of
backslashing.  You need

intranet=# select track_name from ms_track where track_name ~ '^\\(';

to get that backslash into the regex parser.

            regards, tom lane

Re: POSIX RE starting with a (

From
Michael Fuhr
Date:
On Wed, Aug 11, 2004 at 03:28:17PM +0100, Nick Barr wrote:

>======================================================================
>intranet=# select track_id, track_name from ms_track where track_name
>like '(%';
> track_id |           track_name
>----------+---------------------------------
>     1294 | (I Can''t Get No) Satisfaction
>     1340 | (Hidden Track)
>     1503 | (Nice Dream)
>     1942 | (I) Get Lost
>(4 rows)
>
>intranet=# select track_name from ms_track where track_name ~ '^\(';
>ERROR:  invalid regular expression: parentheses () not balanced
>intranet=# select track_name from ms_track where track_name ~ '^(';
>ERROR:  invalid regular expression: parentheses () not balanced
>intranet=# select track_name from ms_track where track_name ~ '^\(';
>ERROR:  invalid regular expression: parentheses () not balanced
>======================================================================
>
>Now I have tried a similar query using the PHP POSIX Regular Expressions
>and it accepts the sequence '^\(' and matches correctly. Is this a
>"feature" of PG that cannot be worked around easily?

See the "Regular Expression Details" section of the PostgreSQL manual:

http://www.postgresql.org/docs/7.4/static/functions-matching.html#POSIX-SYNTAX-DETAILS

The Note under Table 9-12 says, "Remember that the backslash (\)
already has a special meaning in PostgreSQL string literals. To
write a pattern constant that contains a backslash, you must write
two backslashes in the statement."

Try this:

SELECT track_name FROM ms_track WHERE track_name ~ '^\\(';

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: POSIX RE starting with a (

From
Nick Barr
Date:
Tom Lane wrote:
> Nick Barr <nicky@chuckie.co.uk> writes:
>
>>intranet=# select track_name from ms_track where track_name ~ '^\(';
>>ERROR:  invalid regular expression: parentheses () not balanced
>
>
> You've forgotten that the string-literal parser will eat one level of
> backslashing.  You need
>
> intranet=# select track_name from ms_track where track_name ~ '^\\(';
>
> to get that backslash into the regex parser.
>
>             regards, tom lane
>
>
>

Doh,

Thanks guys.


Nick