Let's say I have a program like postgresql 8.2, which currently has 8.4 out at the moment. I have postgresql 8.2 installed and fully functional, where it gets loaded and shutdown on boot up and boot down. Let's say I can't find an svn link to update postgresql to 8.4. I download postgresql 8.4 and install it. Does it automatically overwrite the old version? Will it interfere? What I need to uninstall postgresql 8.2 first? What if I don't have a package manager and what if postgresql 8.2 wasn't installed via package(meaning I can't use removepkg).

How does linux handle these situations?

At the end of the day, to uninstall is to delete... "uninstalling" just knows all the files that were installed, so it can delete them easier (sometimes it changes a config, but usually it's just deletes all appropriate files - including init scripts and the like).

That being said, if you previously compiled and installed a package from source and you you compile and install the new package from source, there's a good chance you will overwrite the last one. Of course, you can change that by passing in "--prefix" to the configure step of compilation. If you do not install the new version over the top of the old version, then you will have two potentially working copies of postgres... your init scripts will be the things that determine which ones are running at any one time.

Will there be conflicts if you run two versions? Almost definitely as they will both be using the same same ports, socket files, etc. - at least without some special configuration.
Will there be conflicts if you overwrite the old one with the new one? Probably not, assuming you properly shut down the old one before overwriting.

If you did not install via a package manager, "linux" knows nothing about what it can potentially run - only what it's currently running, so, that being the case, you're on your own when it comes to removal of the software. That said, often times the makefile that built the app with "make install" will often have a "make uninstall" that will go through and delete all the necessary files. You'll just need to be sure to stop the application before uninstalling.

Just to underline part of what BrianK said, if it's at all possible, I definitely recommend removing the older program first. Also, before you install the new version, I'd suggest using Slackware's tools like makepkg or something that uses them in turn, like SBo's postgres buildscripts to create a proper Slackware package. Then you will be able to use the pkgtools in the future. If you don't have the source or the source lacks the uninstall target, then you'll need to clean out by hand as best you can and hope for the best.