Re: POSIX RE starting with a (

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/