Can you mention some examples of RPGs with weight limits that actually use the mechanic well? I'd be curious to know.
You've already sort of touched on them - survival / scavenging games, and where there are other specific reasons to try to mimic reality as part of gameplay.
I think inventory limits play a key gameplay role in a lot of Rogue variants, too - some games deliberately create "grinding" loops - arguably, this is the sort of mechanic in Rogue variants. Go out, collect loot until you reach a point of satisfaction, pop back to the town level to sell, etc. Restricting PC access to items can be a form of difficulty setting or balance. There's a Steam EA game called Stoneshard where the inventory is really limited. It's designed to restrict you, and make you think about what you need and make trade-offs accordingly. Sometimes, of course, they're just controlling the rate at which you amass money and go into a shop and buy absurdly OP equipment for your level.
Also, Dragon Age: Origins has IMO the most balanced economy of any RPG I've ever played, and it surprised me just how much it enhanced the experience. I wish more devs would pay such care to that aspect.
Money is difficult. Many players collect money as an aim in and of itself even if it's useless - they'll never spend it. But it feels like an achievement to many players, and a lot of games are geared around amassing pointless amounts of money. Sometimes, money is heavily constrained at the start so the player gets in the habit of hyper-looting. If you're casual about looting, eventually it drops a "You need 10k gold to advance this quest [ha ha]!" or you see that magic halberd you really, really want is in a shop at a really high cost. Games can therefore punish you for not being sufficiently aggressive looting.
The values of many things in-game are hopelessly out of whack with history. Even a cheap sword was a couple of day's work for a skilled craftsman. Historically they were affordable even to many peasants, but still a very significant expenditure. You loot 10, even with second hand prices you could live comfortably for months. You definitely shouldn't be selling them for the price of a fish stew. A chainmail shirt was a fuckton of work - a month, maybe more? Really expensive. Why are gems often so cheap in games? If you raid a temple and get a small gold figurine of a cat with emeralds for eyes, it should make you instantly rich. Like, several years annual pay for a skilled craftsman rich. If you did, you wouldn't need to be carting round 10 swords. Sure, some people would try anyway, but.
Games are not necessarily reality simulators, of course, but I wonder if they were more realistic whether it might actually improve things.