What changed from PostgreSQL 8.3 to 9.2 in regex matching?
postgresql, regex
Solution
From changelog of 8.3.2:
Fix a corner case in regular-expression substring matching `(substring(string from pattern))` (Tom) The problem occurs when there is a match to the pattern overall but the user has specified a parenthesized subexpression and that subexpression hasn't got a match. An example is `substring('foo' from 'foo(bar)?')`. This should return NULL, since `(bar)` isn't matched, but it was mistakenly returning the whole-pattern match instead (ie, `foo`)
Problem
If I run this query: ``` SELECT 'Via Orologio 122 A' SIMILAR TO '(Strada|Via) % [0-9]+( [A-Z])?'; ``` I expect to get TRUE. Version 9.1.8 of postgreSQL returns the expected value, but in version 8.3 it returns FALSE. I think that the problem is the final question mark. In fact, the query: ``` SELECT 'Via Orologio 122 A' SIMILAR TO '(Strada|Via) % [0-9]+( [A-Z])'; ``` Returns TRUE in both versions. Anyone knows which is the difference between the two versions?