Suppose you want to match the pattern /branch.\d+/. Well for postgres you need to pass that in as a string and every \ needs to be doubled to escape them because \ already has meaning. So you get 'branch.\\d+'. But in Perl \ is special, so to pass that into postgres you have to write 'branch.\\\\d+'.

If you are very sure that $root has no escape characters in it, you can write something like

$sth_get_branch_list = $dbh->prepare(qq(
                        SELECT branch FROM postgres_database WHERE tre
+e = 'first_tree'
                        AND branch ~ '$root.\\\\d+';
                        ));
[download]

$sth_get_branch_list = $dbh->prepare(q(
                        SELECT branch FROM postgres_database WHERE tre
+e = 'first_tree'
                        AND branch ~ ? || '.\\\\d+';
                        ));
[download]

Try changing your expression to:

$sth_get_branch_list = $dbh->prepare(q(
     SELECT branch FROM postgres_database WHERE tree = 'first_tree'
     AND branch ~ '$root\.\d+';
));
[download]

(You don't use slashes for regular expressions in PostgreSQL, see http://www.postgresql.org/docs/8.3/static/functions-matching.html and http://www.oreillynet.com/pub/a/databases/2006/02/02/postgresq_regexes.html.)

Update: Since I don't use PostgreSQL, there are probably other differences as well.

Update 2: In this case, you really don't need regular expressions anyway, you could use:

$sth_get_branch_list = $dbh->prepare(q(
     SELECT branch FROM postgres_database WHERE tree = 'first_tree'
     AND branch like '$root%';
));
[download]

See now I have tried your first solution but all I get is the following error:

WARNING:  nonstandard use of escape in a string literal
LINE 3:                         AND branch ~ '$root\.\d...
                                             ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'..

Which is confusing me a little :-)

Using bracket expressions to prevent ' leaning toothpick syndrome' (= the \\\\\\ misery) might be useful, and embedded (?x) lets you add comments to a regex, like so:

select 'word.01234' ~ E'(?x)
                        ^                # begin-of-string 
                            word         #   root word
                            [.]          #   dot
                            [[:digit:]]+ #   digits...
                        $                # end-of-string
                     '
;

-- caveat:
--   the (?x) option overrides any previously determined
--   options including RE flavor and case sensitivity.
[download]

tilly is right regarding placeholdering, of course - see also dbi, placeholder and postgres regular expression problem.

I have found another solution actually :-)

$sth_get_branch_list = $dbh->prepare(q(
     SELECT branch FROM postgres_database WHERE tree = 'first_tree'
     AND branch ~ ?;
));

$sth_get_branch_list = $dbh->prepare
 
$sth_get_branch_list->execute('^'.$root.'\.\d+$');

Thanks for the help though :-)
Chris

Using q instead of qq stops interpolation from working, but doesn't make one whit of difference for the syntax errors that he is asking about.

Not one whit, eh? Well, it's true that the code has multiple problems, but it would seem to me you need to have that one little detail right in order to have a hope of getting the right syntax errors...

(By the way: is it my imagination, tilly, or did you just silently do an update to your own post to fix this problem?)