I have a table of filters

create table filter
  "id" serial PRIMARY KEY,
  "user_id" serial REFERENCES public.user (id) ON DELETE CASCADE,
  "keywords" text []

I want to make regex search for a long string, like this

SELECT * FROM public.filter
        WHERE LOWER('some long string') ~ ANY(public.filter.keywords)

Everything works great, but if keywords contain special character, like c++ (check the screen)
How it looks in pgAdmin

I get SQL error

ERROR: invalid regular expression: quantifier operand invalid
SQL state: 2201B

Why it happens and how to avoid this? I'm using NodeJS to insert/select values from PostgreSql

  • The error message says it all. ~ is the regular expression match operator, with the right hand side value being the pattern. Square brackets ([]), plus signs (+), and many other symbols have special meaning in regular expressions. Try to explain in human language what is it that you're trying to achieve, and someone might be able to help. – mustaccio Jan 4 at 1:54
  • @mustaccio ok. I have table filter - it is a table where stored info about user filter by news. For example, user are interseting in news about sport and he/she created filter with some keywords basketball, football, baseball etc. When fresh news are coming into db, I find matches by filter and then notify user about new info. In my example 'some long string' - it is a news. – WhatIsHTML Jan 4 at 11:31
  • So why do you use ~ if your "filter" doesn't contain regular expressions? Maybe you just want =? – a_horse_with_no_name Jan 4 at 12:06
  • @a_horse_with_no_name you're right, really missed it. Thank you! – WhatIsHTML Jan 4 at 12:24
  • @a_horse_with_no_name but = it is a sctrict rule and it will not work if left string will be On this weekend we had a backetball game and keyword basketball. String will not match filter – WhatIsHTML Jan 4 at 12:47

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.