MySQL has a postgres sql_mode. Of course it would be nicer if apps couldn't mess with sql_mode....

No there shouldn't be a mode in PostgreSQL for this. The problem is that this means abandoning the idea of hard data constraints.