So first of all I think the idea that there is a 'tension' is very much overstated. There are many fields in economics that have absorbed behavioral ideas. For instance, the use of exotic preferences that incorporate ambiguity aversion (usually Epstein-Zin) are very popular in macro. I've seen many IO papers that use behavioral effects to explain consumer behavior (for instance, papers that use hyperbolic discounting to explain cost front-loading). The field of behavioral finance is pretty 'mainstream'. In mechanism design, the study of mechanisms that are robust to certain types of agents having certain computational restrictions is becoming popular. In game theory models where at least some agents are boundedly rational are not uncommon. I would also add that much of empirical economics is quasi-experimental and more or less atheoretical and so behavioral effects are unlikely to violate its findings.

Nonetheless of course the vast majority of economic models still assume consumers who are rational in the classical sense. Personally I don't have a problem with this, for a few reasons.

-First of all, you talk about "rigorous psychological studies". But most of these studies involve individuals making very small decisions in a short time frame in a lab setting. I agree that some undergrads sitting in a lab choosing between different gambles with a few dollars at stake will exhibit loss aversion, underweight some probabilities etc. But does that cause me to believe that someone will make similar mistakes when they take a week to make an important career decision? Do I believe a broker whose job it is to maximize his returns, who does this every day and may even have an economics degree will make these mistakes? I am unconvinced.

-Perhaps even more importantly, there has not been much success in actually modelling behavioral consumers. Epstein-Zin preferences for instance, do not seem to explain financial data all that much better than standard utility models. Indeed, by their very nature behavioral effects are hard to model, the use of specific heuristics to make decisions is far more complex than simply modelling the true optimum.

-Economic models shouldn't be taken too literally anyway. A model of moral hazard that uses utility maximizing agents isn't intended to exactly fit the world, rather it is there to showcase some kind of mechanism or effect. A structural empirical model provides a framework to allow us to interpret coefficients, to avoid some kinds of empirical bias, but still does not need to be an exact replica of reality. It is not simply enough to assert that the potential approximation implicit in assuming classically rational behavior is an approximation, rather you have to suggest a reason that it might be enough to undermine the conclusions. In the same way that it is not enough just to point out all the other things missing from the model (lack of financial sector in a macro model, undifferentiated products in a micro model etc.)

In general I think that behavioral economics is useful and insightful, but that behavioral effects should be incorporated in a model on a case by case basis and only where a good argument can be made that they are relevant and appropriate: For instance, is there some effect that economic theory struggles to explain but that behavioral effects could be behind? Does the data suggest implausible parameters in a structural model based on standard theory but not in one based on one with these behavioral preferences? At the moment this seems to be how the literature works and I think that is a good thing.

Just to clarify my point, suppose you are at a seminar and someone is presenting a macro paper that aims to explain some feature of the labor market. If you put your hand up and say "Ah but this model of yours assumes a perfect financial market" the presenter will say "well yes we do assume that, but we don't see why that is important?". Now if you said "Ah but this model of yours assumes a perfect financial market, and if the financial market were imperfect then your conclusion that people can insure themselves against unemployment may not be true and your conclusions may not hold" well then the presenter will have to take you seriously. Behavioral effects are no different, they are an additional feature a model that increases its complexity and reduces its tractability, it is not enough to point out that ignoring them is an approximation of reality, you need to find a reason why in a specific case that approximation makes a result unreliable.

EDIT: Here are some links to papers I mention in passing.

This paper uses hyperbolic discounting to explain why yearly membership in health clubs costs more than paying per month, spurred a number of empirical IO papers that do a similar thing http://eml.berkeley.edu/~sdellavi/wp/gymempAER.pdf

This is the extremely influential paper by Epstein and Zin that explains how their model of preferences that separates the intertemporal elasticity of substitution and risk aversion can better explain puzzles in financial data. http://pages.stern.nyu.edu/~dbackus/Exotic/1Time%20and%20risk/EpsteinZin%20JPE%2091.pdf

Here is a recent working paper in mechanism design that provides a sense in which mechanisms can be strategy proof and robust to some kinds of computational restrictions on the part of agents and shows this can explain some previously unexplained empirical facts: https://site.stanford.edu/sites/default/files/osp_groundwork_0.pdf

Oh and in defense of classic rationality and for a bit of fun, here is a paper that shows chipanzees in competitive games play mixed strategy Nash equilibrium probabilities! http://www.nature.com/articles/srep05182