While I voted for Silent Hill, another awesome gem you should look to if you have a PS3 is Siren: Blood Curse. It's made by Project Siren, largely made up of people from Team Silent behind Silent Hill. It's a reimagining of the original Siren which is scary and difficult as hell in its own right, but perhaps too difficult to the point of frustration.
For me, Blood Curse is awesome. It's broken up into 12 episodes, and it's creepy as hell. Your best bet (and much of the time, only option) is hiding from the Shibito, who basically look like zombies but they attack and move like people, or distracting them. Sometimes you can take them out head-on, but it's highly discouraged in most cases. The story is just flat out weird though, and it basically requires you to find the backstory items in the game to completely understand what's happening.
EDIT: What makes Siren so unique is the gameplay element it employs called sight jacking. At any time, the character can actually split the screen vertically, and while you can move around and such normally on the left side, the right side allows you to switch through the views of the various Shibito around them (it's a plot device, don't wanna spoil how they can do it). This is to better help with planning a distraction or escape route through the level.
That being said, the game is absolutely nerve-wracking. It's a genre I like to call stealth horror, and it works really well in this game. Definitely in my top five horror games.
Other recommendations: - Alan Wake. It's more action horror, but it's got an amazing story and some creepy atmosphere. - Slender. Proof that indie gaming can be great. Such a simple premise becomes a heartpounding thriller of a game. - Amnesia. Probably one of, if not the scariest game I've ever played. It really sucks you in and doesn't let go. --- I don't see Nintendo, Microsoft, Sony. I see Mario, Zelda, Uncharted, Resident Evil, Gears of War, Alan Wake, Heavy Rain, etc. Play games, not companies.