First off what does RPM stand for?
Is it RPM Package Manager?

Secondly are RPM's only good for RedHat. I've installed an RPM on mandrake (Opera 7). But in general I can only find rpms for redhat (like at postgresql.org).

How come rpm's are almost always only available for redhat?

rpm stands for redhat package manager. They do have rpms for other systems, mainly mandrake. That is the way redhat and mandrake install packages, kind of like .debs in debian, and hell, an exe in windows (to an extend).

Exe's are different, there are self extracting compression formats (sorta like .bin in linux) ... and with that we are no officially on really (and i mean REALLY shaky ground).

there is no true equivalent to .rpm, .deb in windows really because of the structuring that can be put in an .rpm . that said Windows actually handles it better in that the packages are more inclusive, the downside is that alot of redundancy hence is prone to exist in windows.

RPMs work fine on most distros because it has become a standard package format, that said, an redhat .rpm may not work fine on a SuSE base, because of differences (for instance in the XServer architecture). You can convert .rpm to .tar for example for use with Slackware via rpm2tar, which works surprisingly well.

RPM based distributions generally have dependency issues though, you download the rpm you want only to find you need more to get the original thing to work. .deb works better if you can resort to it's online facilities, and i found that debian has better search engines if you need to manually work on resolving dependencies cause you need the control.

.tar so far is great, but going from source seems to be the best, provided there are no bugs in the source

Yeah, upgrading a lot of packages on an RPM system can be troublesome. I guess the closest equivalent to an RPm on windows is a zip file, but it contains a pre-installed program and you just unzip it to your C drive. Some of them also contain scripts to set up the software.
Debains package format is probably the best IMO, as it supports dependencies, but instead of just spouting a pile of package names that you have to go and sour the web for a copy to match your distro, it will just fetch them automatically from the debian servers.
The way distros differ is the layout of the system, so as chakkerz says, a SUSE rpm may not work on a Mandrake system, but then again it might. The best place to start looking for RPMs would be the distros website. Most provide a tool to search for them and grab them, but for some of these you have to pay for.

SuSe were in talking to my company a couple of months back and told me that RPM _actually_ stood for Reliable Package Manager and that people only _thought_ it stood for Redhat Package Manager. Take that as you will. ;>

apt4rpm.sourceforge.net

Also check out make_uninstall from freshmeat. If you can't find RPM's then make your own and set up an RPM repository for apt4rpm.

Flibble