On the postgres command line, I tried this:

regexp_matches('file?device_id[]=555', 'device_id\[\]=(\d+)', 'g') as dev_id;

I get this error:

ERROR:  invalid regular expression: brackets [] not balanced

When I run this same query in HeidiSQL it returns with no errors. I don't understand why the CLI thinks the brackets are unbalanced when I'm escaping them. Just for testing, I tried anywhere from 0 to 3 backslashes to escape the brackets, and none of those worked.

  • Gives dev_id {555} for me!
    – Vérace
    Dec 7, 2021 at 15:36
  • How exactly do you run this "on the postgres command line"? Because this works for me in psql
    – user1822
    Dec 7, 2021 at 15:58
  • @a_horse_with_no_name I started psql on our local server and ran it there. We have postgres version 9.5.6. I'm not sure if that's a factor.
    – raphael75
    Dec 7, 2021 at 16:02
  • Works just fine on 9.5.25 - not sure if there was a major functionality shift between 9.5.6 and .25 - but, any and all of the 9 versions are now End of Life and no longer supported - time for an upgrade!
    – Vérace
    Dec 7, 2021 at 16:06

1 Answer 1


I managed to find the answer in this thread:


After running

set standard_conforming_strings=1;

It now returns the expected 555.

  • Must be an issue with your server setup - as far as I can see, the normal setup returns the correct answer!
    – Vérace
    Dec 7, 2021 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.