What has changed from PostgreSQL 8.3 to 9.2 when matching regular expressions?

Question

If I run this query:

SELECT 'Via Orologio 122 A' SIMILAR TO '(Strada|Via) % [0-9]+( [AZ])?'; 

I expect to get TRUE. Version 9.1.8 postgreSQL returns the expected value, but in version 8.3 it returns FALSE. I think the problem is the last question mark. In fact, the request is:

 SELECT 'Via Orologio 122 A' SIMILAR TO '(Strada|Via) % [0-9]+( [AZ])'; 

Returns TRUE in both versions.

Does anyone know what the difference is between the two versions?

Answer 1

From change log 8.3.2 :

Correct angular case in substring of regular expression (substring(string from pattern)) (Volume)
The problem occurs when it is a match with the template as a whole, but the user specified a nested subexpression and the subexpression did not receive a match. Example: substring('foo' from 'foo(bar)?') . This should return NULL, since (bar) does not match, but fails (instead of foo )

Answer 2

When switching to regex ( ~ ), the replacement replacement will be as follows:

 SELECT 'Via Orologio 122 A' ~ '^(?:(?:Strada|Via) .* [0-9]+(?: [AZ])?)$' 

• left and right tethered
• c * , not +
• not exciting parentheses

Hint:
You can let Postgres translate SIMILAR TO expressions for you using the method described in the corresponding answer to dba.SE.

Answer 3

Following Craig Ringer's advice, go to:

 SELECT 'Via Orologio 122 A' ~ '(Strada|Via) .+ [0-9]+( [AZ])?'; 

solved a problem. "~" seems certainly a better solution than "SIMILAR"