0

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.

Improve this question
4
  • Gives dev_id {555} for me! Commented Dec 7, 2021 at 15:36
  • How exactly do you run this "on the postgres command line"? Because this works for me in psql Commented 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. Commented 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! Commented Dec 7, 2021 at 16:06

1 Answer 1

Reset to default
0

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.

Improve this answer
1
  • Must be an issue with your server setup - as far as I can see, the normal setup returns the correct answer! Commented 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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.