Error ошибка неверное регулярное выражение quantifier operand invalid - Решение и исправление самых разных ошибок на TopOshibok.ru

So I’m getting an error message when running regexp_matches on postgres and cannot figure out how to get passed it. It appears to work fine on reg_exp test sites like regex101 but unfortunately does not work when applied practically. The error message is:

ERROR: invalid regular expression: quantifier operand invalid

select email, regexp_matches(email,'^[a-zA-Z0-9]{4}+[0-9]{6}') as pattern
from table
limit 10

It’s a relatively simple pattern of 4 alphabet characters and 6 numbers within an email prefix. Perhaps ‘^[a-zA-Z0-9]{4}+[0-9]{6}’ might not be the best way? Any suggestions would be much appreciated. Many thanks!

asked Mar 2, 2018 at 13:16

A ^[a-zA-Z0-9]{4}+[0-9]{6} pattern is not compatible with the PostgreSQL regexp engine because it does not support possessive quantifiers. {4}+ is a possessive limiting quantifier here that match 4 occurrences of the quantified pattern without any possibility to backtrack into these 4 chars. Since the max argument is not used (i.e. not something like {4,7}), the {4}+ possessive quantifier works the same way as {4}.

Use {4} instead of {4}+:

select email, regexp_matches(email,'^[a-zA-Z0-9]{4}[0-9]{6}') as pattern
from table
limit 10

Besides, if the pattern is to match the entire record, add $ (end of string) anchor at the end of the pattern:

'^[a-zA-Z0-9]{4}[0-9]{6}$'

answered Mar 2, 2018 at 13:26

Wiktor StribiżewWiktor Stribiżew

609k39 gold badges449 silver badges565 bronze badges

Источник

Всем привет. Есть таблица фильтров по книгам.

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

При добавлении новой книги в базу, идет поиск по уже существующим фильтрам и ключевым словам в них, чтобы уведомлять пользователя о новом поступлении.
Добавление книги. Используется pg-promise

self.create = function (obj) {
        return db.query(`INSERT INTO public.filter (user_id, keywords) 
        VALUES ($(userId), $(keywords)) RETURNING id;`, obj);
};

Поиск по ключевым словам выглядит примерно так. На вход у нас попадает строка(кусок текста)

SELECT * FROM public.filter WHERE
LOWER('Some long string from a book') ~ ANY(public.filter.keywords);

И если в фильтре в ключевых словах содержится какой-то специальный символ, например (c++), то вылетает ошибка

ERROR:  ОШИБКА:  неверное регулярное выражение: quantifier operand invalid

SQL state: 2201B

Из-за чего так?

Источник

Impacted versions: 14.0

Steps to reproduce:
os : windows
postgres -V : 13.3
Log in as Admin
Navigate to Invoicing
Click to Create
Current behavior: It throws the Server Error.

Expected behavior: It’ll not be thrown any errors.

Error Info:

2021-08-28 16:20:06,086 46220 ERROR toto odoo.sql_db: bad query:
UPDATE account_move SET write_date = write_date WHERE id = (
SELECT id FROM account_move
WHERE journal_id = 2 AND name != ‘/’ AND sequence_prefix !~ ‘^(?:.?)(?:((?<=\D)|(?<=^))((20|21)?\d{2}))(?:\D+?)$’ AND move_type NOT IN (‘out_refund’, ‘in_refund’)
AND sequence_prefix = (SELECT sequence_prefix FROM account_move WHERE journal_id = 2 AND name != ‘/’ AND sequence_prefix !~ ‘^(?:.?)(?:((?<=\D)|(?<=^))((20|21)?\d{2}))(?:\D+?)$’ AND move_type NOT IN (‘out_refund’, ‘in_refund’) ORDER BY id DESC LIMIT 1)
ORDER BY sequence_number DESC
LIMIT 1
)
RETURNING name;

ERROR: invalid regular expression: quantifier operand invalid

2021-08-28 16:20:06,087 46220 ERROR toto odoo.http: Exception during JSON request handling.
Traceback (most recent call last):
File «C:\Program Files (x86)\odoo-14\server\odoo\addons\base\models\ir_http.py», line 237, in _dispatch
result = request.dispatch()
File «C:\Program Files (x86)\odoo-14\server\odoo\http.py», line 683, in dispatch
result = self._call_function(**self.params)
File «C:\Program Files (x86)\odoo-14\server\odoo\http.py», line 359, in _call_function
return checked_call(self.db, *args, **kwargs)
File «C:\Program Files (x86)\odoo-14\server\odoo\service\model.py», line 94, in wrapper
return f(dbname, *args, **kwargs)
File «C:\Program Files (x86)\odoo-14\server\odoo\http.py», line 347, in checked_call
result = self.endpoint(*a, **kw)
File «C:\Program Files (x86)\odoo-14\server\odoo\http.py», line 912, in call
return self.method(*args, **kw)
File «C:\Program Files (x86)\odoo-14\server\odoo\http.py», line 531, in response_wrap
response = f(*args, **kw)
File «C:\Program Files (x86)\odoo-14\server\odoo\addons\web\controllers\main.py», line 1389, in call_kw
return self._call_kw(model, method, args, kwargs)
File «C:\Program Files (x86)\odoo-14\server\odoo\addons\web\controllers\main.py», line 1381, in _call_kw
return call_kw(request.env[model], method, args, kwargs)
File «C:\Program Files (x86)\odoo-14\server\odoo\api.py», line 396, in call_kw
result = _call_kw_multi(method, model, args, kwargs)
File «C:\Program Files (x86)\odoo-14\server\odoo\api.py», line 383, in _call_kw_multi
result = method(recs, *args, **kwargs)
File «f:\todoencloud\addons\account_invoice_date_due\models\account_move.py», line 34, in onchange
return super(AccountMove, obj).onchange(values, field_name, field_onchange)
File «C:\Program Files (x86)\odoo-14\server\odoo\addons\account\models\account_move.py», line 1059, in onchange
return super(AccountMove, self.with_context(recursive_onchanges=False)).onchange(values, field_name, field_onchange)
File «C:\Program Files (x86)\odoo-14\server\odoo\models.py», line 6230, in onchange
record._onchange_eval(name, field_onchange[name], result)
File «C:\Program Files (x86)\odoo-14\server\odoo\models.py», line 5986, in _onchange_eval
method_res = method(self)
File «C:\Program Files (x86)\odoo-14\server\odoo\addons\account\models\account_move.py», line 384, in _onchange_journal
if self.state == ‘draft’ and self._get_last_sequence() and self.name and self.name != ‘/’:
File «C:\Program Files (x86)\odoo-14\server\odoo\addons\account\models\sequence_mixin.py», line 179, in get_last_sequence
self.env.cr.execute(query, param)
File «f:\todoencloud\addons\slow_statement_logger_init.py», line 38, in execute
return super().execute(query, params, log_exceptions)
File «», line 2, in execute
File «C:\Program Files (x86)\odoo-14\server\odoo\sql_db.py», line 101, in check
return f(self, *args, **kwargs)
File «C:\Program Files (x86)\odoo-14\server\odoo\sql_db.py», line 298, in execute
res = self._obj.execute(query, params)
Exception

The above exception was the direct cause of the following exception:

Источник

Всем привет. Есть таблица фильтров по книгам.

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

self.create = function (obj) {
        return db.query(`INSERT INTO public.filter (user_id, keywords) 
        VALUES ($(userId), $(keywords)) RETURNING id;`, obj);
};

Поиск по ключевым словам выглядит примерно так. На вход у нас попадает строка(кусок текста)

SELECT * FROM public.filter WHERE
LOWER('Some long string from a book') ~ ANY(public.filter.keywords);

И если в фильтре в ключевых словах содержится какой-то специальный символ, например (c++), то вылетает ошибка

ERROR:  ОШИБКА:  неверное регулярное выражение: quantifier operand invalid

SQL state: 2201B

Из-за чего так?

Impacted versions: 14.0

Steps to reproduce:
os : windows
postgres -V : 13.3
Log in as Admin
Navigate to Invoicing
Click to Create
Current behavior: It throws the Server Error.

Expected behavior: It’ll not be thrown any errors.

Error Info:

2021-08-28 16:20:06,086 46220 ERROR toto odoo.sql_db: bad query:
UPDATE account_move SET write_date = write_date WHERE id = (
SELECT id FROM account_move
WHERE journal_id = 2 AND name != ‘/’ AND sequence_prefix !~ ‘^(?:.?)(?:((?<=D)|(?<=^))((20|21)?d{2}))(?:D+?)$’ AND move_type NOT IN (‘out_refund’, ‘in_refund’)
AND sequence_prefix = (SELECT sequence_prefix FROM account_move WHERE journal_id = 2 AND name != ‘/’ AND sequence_prefix !~ ‘^(?:.?)(?:((?<=D)|(?<=^))((20|21)?d{2}))(?:D+?)$’ AND move_type NOT IN (‘out_refund’, ‘in_refund’) ORDER BY id DESC LIMIT 1)
ORDER BY sequence_number DESC
LIMIT 1
)
RETURNING name;

ERROR: invalid regular expression: quantifier operand invalid

2021-08-28 16:20:06,087 46220 ERROR toto odoo.http: Exception during JSON request handling.
Traceback (most recent call last):
File «C:Program Files (x86)odoo-14serverodooaddonsbasemodelsir_http.py», line 237, in _dispatch
result = request.dispatch()
File «C:Program Files (x86)odoo-14serverodoohttp.py», line 683, in dispatch
result = self._call_function(**self.params)
File «C:Program Files (x86)odoo-14serverodoohttp.py», line 359, in _call_function
return checked_call(self.db, *args, **kwargs)
File «C:Program Files (x86)odoo-14serverodooservicemodel.py», line 94, in wrapper
return f(dbname, *args, **kwargs)
File «C:Program Files (x86)odoo-14serverodoohttp.py», line 347, in checked_call
result = self.endpoint(*a, **kw)
File «C:Program Files (x86)odoo-14serverodoohttp.py», line 912, in call
return self.method(*args, **kw)
File «C:Program Files (x86)odoo-14serverodoohttp.py», line 531, in response_wrap
response = f(*args, **kw)
File «C:Program Files (x86)odoo-14serverodooaddonswebcontrollersmain.py», line 1389, in call_kw
return self._call_kw(model, method, args, kwargs)
File «C:Program Files (x86)odoo-14serverodooaddonswebcontrollersmain.py», line 1381, in _call_kw
return call_kw(request.env[model], method, args, kwargs)
File «C:Program Files (x86)odoo-14serverodooapi.py», line 396, in call_kw
result = _call_kw_multi(method, model, args, kwargs)
File «C:Program Files (x86)odoo-14serverodooapi.py», line 383, in _call_kw_multi
result = method(recs, *args, **kwargs)
File «f:todoencloudaddonsaccount_invoice_date_duemodelsaccount_move.py», line 34, in onchange
return super(AccountMove, obj).onchange(values, field_name, field_onchange)
File «C:Program Files (x86)odoo-14serverodooaddonsaccountmodelsaccount_move.py», line 1059, in onchange
return super(AccountMove, self.with_context(recursive_onchanges=False)).onchange(values, field_name, field_onchange)
File «C:Program Files (x86)odoo-14serverodoomodels.py», line 6230, in onchange
record._onchange_eval(name, field_onchange[name], result)
File «C:Program Files (x86)odoo-14serverodoomodels.py», line 5986, in _onchange_eval
method_res = method(self)
File «C:Program Files (x86)odoo-14serverodooaddonsaccountmodelsaccount_move.py», line 384, in _onchange_journal
if self.state == ‘draft’ and self._get_last_sequence() and self.name and self.name != ‘/’:
File «C:Program Files (x86)odoo-14serverodooaddonsaccountmodelssequence_mixin.py», line 179, in get_last_sequence
self.env.cr.execute(query, param)
File «f:todoencloudaddonsslow_statement_logger_init.py», line 38, in execute
return super().execute(query, params, log_exceptions)
File «», line 2, in execute
File «C:Program Files (x86)odoo-14serverodoosql_db.py», line 101, in check
return f(self, *args, **kwargs)
File «C:Program Files (x86)odoo-14serverodoosql_db.py», line 298, in execute
res = self._obj.execute(query, params)
Exception

The above exception was the direct cause of the following exception:

This:

UPDATE master_list
SET group = 'JustAdoptingAdvice' 
WHERE group = 'JustAdoptedAdvice+';

should work perfectly fine if the field was really JustAdoptedAdvice+, but I don’t think it is. psql is showing that + to mean continuation … I suspect there’s actually a newline there.

UPDATE master_list
SET group = 'JustAdoptingAdvice' 
WHERE group = E'JustAdoptedAdvicen';

There could be whitespace after the newline too, so maybe even:

UPDATE master_list
SET group = 'JustAdoptingAdvice' 
WHERE group ~ 'JustAdoptedAdvice[[:blank:]n]+';

which says «JustAdoptedAdvice followed by one or more of tab/space/newline».

Demo showing it’s likely to be a newline:

regress=> CREATE TABLE t4(x text);
CREATE TABLE
regress=> INSERT INTO t4(x) VALUES (E'JustAdoptedAdvicen');
INSERT 0 1
regress=> SELECT * FROM t4;
         x         
-------------------
 JustAdoptedAdvice+

(1 row)

To be sure, look at the hex representation of the utf-8 encoding of the string:

regress=> SELECT encode( convert_to(x, 'utf-8'), 'hex') FROM t4;
                encode                
--------------------------------------
 4a75737441646f707465644164766963650a
(1 row)

You’ll see that it ends with 0x0a, which is a newline.

ERROR: invalid regular expression: quantifier operand invalid

select email, regexp_matches(email,'^[a-zA-Z0-9]{4}+[0-9]{6}') as pattern
from table
limit 10

asked Mar 2, 2018 at 13:16

Use {4} instead of {4}+:

select email, regexp_matches(email,'^[a-zA-Z0-9]{4}[0-9]{6}') as pattern
from table
limit 10

Besides, if the pattern is to match the entire record, add $ (end of string) anchor at the end of the pattern:

'^[a-zA-Z0-9]{4}[0-9]{6}$'

answered Mar 2, 2018 at 13:26

Wiktor StribiżewWiktor Stribiżew

595k36 gold badges416 silver badges527 bronze badges

ERROR: invalid regular expression: quantifier operand invalid

select email, regexp_matches(email,'^[a-zA-Z0-9]{4}+[0-9]{6}') as pattern
from table
limit 10

asked Mar 2, 2018 at 13:16

Use {4} instead of {4}+:

select email, regexp_matches(email,'^[a-zA-Z0-9]{4}[0-9]{6}') as pattern
from table
limit 10

Besides, if the pattern is to match the entire record, add $ (end of string) anchor at the end of the pattern:

'^[a-zA-Z0-9]{4}[0-9]{6}$'

answered Mar 2, 2018 at 13:26

Wiktor StribiżewWiktor Stribiżew

595k36 gold badges416 silver badges527 bronze badges

ERROR: invalid regular expression: quantifier operand invalid

select email, regexp_matches(email,'^[a-zA-Z0-9]{4}+[0-9]{6}') as pattern
from table
limit 10

asked Mar 2, 2018 at 13:16

Use {4} instead of {4}+:

select email, regexp_matches(email,'^[a-zA-Z0-9]{4}[0-9]{6}') as pattern
from table
limit 10

Besides, if the pattern is to match the entire record, add $ (end of string) anchor at the end of the pattern:

'^[a-zA-Z0-9]{4}[0-9]{6}$'

answered Mar 2, 2018 at 13:26

Wiktor StribiżewWiktor Stribiżew

595k36 gold badges416 silver badges527 bronze badges

Impacted versions: 14.0

Steps to reproduce:
os : windows
postgres -V : 13.3
Log in as Admin
Navigate to Invoicing
Click to Create
Current behavior: It throws the Server Error.

Expected behavior: It’ll not be thrown any errors.

Error Info:

2021-08-28 16:20:06,086 46220 ERROR toto odoo.sql_db: bad query:
UPDATE account_move SET write_date = write_date WHERE id = (
SELECT id FROM account_move
WHERE journal_id = 2 AND name != ‘/’ AND sequence_prefix !~ ‘^(?:.?)(?:((?<=D)|(?<=^))((20|21)?d{2}))(?:D+?)$’ AND move_type NOT IN (‘out_refund’, ‘in_refund’)
AND sequence_prefix = (SELECT sequence_prefix FROM account_move WHERE journal_id = 2 AND name != ‘/’ AND sequence_prefix !~ ‘^(?:.?)(?:((?<=D)|(?<=^))((20|21)?d{2}))(?:D+?)$’ AND move_type NOT IN (‘out_refund’, ‘in_refund’) ORDER BY id DESC LIMIT 1)
ORDER BY sequence_number DESC
LIMIT 1
)
RETURNING name;

ERROR: invalid regular expression: quantifier operand invalid

The above exception was the direct cause of the following exception:

[Solved]

Hello guys,I have a problem, which brakes my search bar.How I got to the problem: I had SQLite3 and everthing worked fine. After accidentaly destroying my database, I went to PostgreSQL. There the error began.When I search in my searchbar this: «?» or everthing relatet to it, for example: «How to fix my problem?» then django.utils gives me an error caused by Postgres:FEHLER: ungültiger regulärer Ausdruck: quantifier operand invalidIt says, the quantifier operand «?» is invalid. But why, how can I solve this problem?I tried to change «?» to «?» and «?», because the question mark could be a special character for the SQL… When I try with «?» and «?», the error doesn’t come, but it doesn’t search properly how it should.

Traceback (most recent call last):
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangodbbackendsutils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.InvalidRegularExpression: FEHLER:  ungültiger regulärer Ausdruck: quantifier operand invalid

The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangocorehandlersexception.py", line 47, in inner
    response = get_response(request)
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangocorehandlersbase.py", line 179, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangoviewsgenericbase.py", line 70, in view
    return self.dispatch(request, *args, **kwargs)
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangoviewsgenericbase.py", line 98, in dispatch
    return handler(request, *args, **kwargs)
  File "C:UsersDesktopDjangodjango_projectblogviews.py", line 78, in get
    print(posts)
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangodbmodelsquery.py", line 263, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangodbmodelsquery.py", line 287, in __iter__
    self._fetch_all()
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangodbmodelsquery.py", line 1308, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangodbmodelsquery.py", line 53, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangodbmodelssqlcompiler.py", line 1156, in execute_sql
    cursor.execute(sql, params)
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangodbbackendsutils.py", line 98, in execute
    return super().execute(sql, params)
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangodbbackendsutils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangodbbackendsutils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangodbbackendsutils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangodbutils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:UsersDesktopDjangovenvlibsite-packagesdjangodbbackendsutils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.DataError: FEHLER:  ungültiger regulärer Ausdruck: quantifier operand invalid

When I search for «102 hello for you ?», then the script splittes it to an array(«100», «hello», «for», «you?» and inserts every element called «w» in the filter command below)

I access the database by Django filter command:

posts = Post.objects.filter((Q(title__iregex=w) & Q(title__icontains=w)) | (Q(content__iregex=w) & Q(content__icontains=w))).order_by('-date_posted')[:20]

Does anyone know how I could solve the problem? If you need more information, I can give it!Thanks for any help

This:

UPDATE master_list
SET group = 'JustAdoptingAdvice' 
WHERE group = 'JustAdoptedAdvice+';

UPDATE master_list
SET group = 'JustAdoptingAdvice' 
WHERE group = E'JustAdoptedAdvicen';

There could be whitespace after the newline too, so maybe even:

UPDATE master_list
SET group = 'JustAdoptingAdvice' 
WHERE group ~ 'JustAdoptedAdvice[[:blank:]n]+';

which says «JustAdoptedAdvice followed by one or more of tab/space/newline».

Demo showing it’s likely to be a newline:

regress=> CREATE TABLE t4(x text);
CREATE TABLE
regress=> INSERT INTO t4(x) VALUES (E'JustAdoptedAdvicen');
INSERT 0 1
regress=> SELECT * FROM t4;
         x         
-------------------
 JustAdoptedAdvice+

(1 row)

To be sure, look at the hex representation of the utf-8 encoding of the string:

regress=> SELECT encode( convert_to(x, 'utf-8'), 'hex') FROM t4;
                encode                
--------------------------------------
 4a75737441646f707465644164766963650a
(1 row)

You’ll see that it ends with 0x0a, which is a newline.

Источник

am trying the following regex_replace statement in PostgreSQL (9.2)

 # SELECT regexp_replace('_this._is_a_long_entry._of.nonsense_text', '\._|^_', '$','g');

Objective:

is to replace all underscore characters preceded by a period with ‘$’. Am also replacing the underscore anchored at position 1; this is working. I’d like to cram both matches into the one statement. All of this is to develop a mechanism for pushing some weird text we have into a PostreSQL ltree structure.

Issue:

How to make the first replacement above work, without molesting the period character?

I’d like to see the result look like this:

$this.$is_a_long_entry.$of.nonsense_text

Note: Have also tried explicit capture of the underscore, but the PG implementation seems to ignore this:

# SELECT regexp_replace('_this.is_long_entry._of.nonsense_text', '\.(_)|^_', '$','g');

Источник