I never owned a PS, so I never did get in on the Silent Hill or Resident Evil games. However, there have been several games that creeped me out more than a little.
Doom 3 is worth mentioning, but not really that bad. It's really creepy, sure, but with the kind of butt you can kick in that game, it removes a little from the scariness. Still, there's blood everywhere and you're fighting DEMONS in a sci-fi setting. It can get pretty intense.
F.E.A.R. is a really creepy game. Once you put aside the childish meaning of the acronym (First Encounter Action Recon), it's a very creepy game involving cannibalism, creepy ghost stuff, psychic projections, and even a psuedo Japanese horrer girl, scraggly hair and all, who likes to giggle while she liquifies screaming Delta Force teams, leaving nothing but several gallons of blood and liquid gore pooled around bare skeletons. Even though there's only one part of the game where she can actually hurt you, anybody who gets into the ambiance of the game will start hiding under the computer desk whenever she appears.
Another top spot goes to Eternal Darkness. The creepy plot isn't 100% original, but it borrows a bit from Lovecraft, so it's OK. The thing that really creeped me out about this game, though, is the sanity system, which causes all kinds of on-screen hallucinations when your sanity drops. It has a couple of standard game ones, such as hallucinated monster fights, wierd traps, etc. It also has a couple of wierd ones, like limbs randomly falling off, then reappearing, constant water dripping, and whispering voices. However, the truly creepy ones are the ones that get to you, the player. Sometimes, during a fight, a box will pop up claiming that your controller is unplugged, but the game doesn't pause and you can still control your character. Sometimes, the screen will go black and 'VIDEO1' will be displayed in the corner, making it look like the GameCube is unplugged. One of the wierdest ones, though, is that your screen slowly tilts to the side as you go insane. Slowly enough, in fact that you will often catch yourself unconciously tilting your head. It really creeps me out.
The creepiest, however, was System Shock 2. You KNOW Shodan is watching EVERYTHING you do, and she often appears to let you know how inferior you are. Since she controls the whole station, you get the distinct impression that she's toying with you, and that whatever fun you are to her is the only reason you're still alive. Then there are the multiple times she pops up on every screen in the room to taunt you mercilessly or tell you why what you just did was really stupid. It was that feeling of being watched that really got to me, and it's also why I'm looking forward to BioShock, System Shock's 'spiritual' successor.