Using POSIX Regular Expressions on xml type fields gives inconsistent results
От | Denis Papathanasiou |
---|---|
Тема | Using POSIX Regular Expressions on xml type fields gives inconsistent results |
Дата | |
Msg-id | 50D3B39B.20804@banrai.com обсуждение исходный текст |
Ответы |
Re: Using POSIX Regular Expressions on xml type fields gives inconsistent results
Re: Using POSIX Regular Expressions on xml type fields gives inconsistent results |
Список | pgsql-general |
I have a table with an xml type column, and while I can make regex queries like this successfully: => select id from form_d where 'kumar' ~* any( CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as TEXT[]) ); id -------------------------------------- 97e1541b-27f4-4d95-beb5-2f67830ebc48 (1 row) and => select id from form_d where '(kumar|gonzales)' ~* any( CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as TEXT[]) ); id -------------------------------------- aea32e7e-f422-405c-953b-86fe3c8c1e30 97e1541b-27f4-4d95-beb5-2f67830ebc48 (2 rows) I.e., they are successful in that the last names in the xml data are "Kumar" and "Gonzales", so the ~* operator handled the case comparison correctly, and the (|) grouping also found the two rows where the corresponding xml had "Kumar" and "Gonzales" in the PersonList attribute. But if I change the expression to ask for all last names beginning with "Kuma" or "Gonza", like this, the query returns no matches: => select id from form_d where '^kuma' ~* any( CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as TEXT[]) ); id ---- (0 rows) => select id from form_d where '^gonza' ~* any( CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as TEXT[]) ); id ---- (0 rows) => select id from form_d where '^(kuma|gonza)' ~* any( CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as TEXT[]) ); id ---- (0 rows) Why is that?
В списке pgsql-general по дате отправления: