postgres error: invalid regular expression: brackets [] not balanced

Question

On the postgres command line, I tried this:

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

• Vérace: Gives dev_id {555} for me!
• user1822: How exactly do you run this "on the postgres command line"? Because this works for me in psql
• raphael75: @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.
• Vérace: 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!

Answer 1

I managed to find the answer in this thread:

https://stackoverflow.com/questions/618350/escaping-bracket-in-postgresql-query

After running

set standard_conforming_strings=1;

It now returns the expected 555.

• Vérace: Must be an issue with your server setup - as far as I can see, the normal setup returns the correct answer!