Until recent versions CTEs could be very inefficient where they would not be in other databases, though that has now been addressed unless you are running an older release. I'm can't think of any major data loss stories, though there have been one or two "why we switched from pg to <whatever>" ones.