this is supposed to find those to have 2 words and more.
select name FROM a_table where "STREET_NAME" ~ '^[[:alpha:]+ ]+[:alpha:]+$';
But, it finds only one word as well.
It appears that regex is not robust.
Can anyone shed light on this?
You put the space inside the first character class (the plus sign within that class is also a literal plus, not a "one or more" indicator) thus you've made the space itself optional. Did you maybe mean to use ( ) parens instead of [ ]?
When asking regex questions it is usually more helpful to formulate them like so: