postgreSQL mysql oracle differences

Question

I'm having to start building the architecture for a database project but i really don't know the differences between the engines.

Anyone can explain whats the pros and bads of each of these three engines? We'll have to choose one of them and the only thing I actually know about them is this:

• Mysql & Postgres:
  • Are free but not so good as oracle
  • Mysql as security problems (is this true?)
• Oracle:
  • Best data base engine in the world
  • Expensive

Can someone clear out other differences between them? This is a medium/large (we're thinking of around some 100 to 200 tables) project with low budget, what would you choose? And with a higher budget?

Answer 1

A few years ago I had to write a translation engine; you feed it one set of sql and it translates to the dialect of the currently connected engine. My engine works on Postgres (AKA PostgreSql), Ingres, DB2, Informix, Sybase, and Oracle - oh, and ANTS. Frankly, Oracle is my least favorite (more on that below)... Unfortunately for you, mySql and SQL Server are not on the list (at the time neither was considered a serious RDBMS - but times do change).

Without regard to the quality or performance of the engine - and ease of making and restoring backups - here are the primary areas of difference:

• datatypes
• limits
• invalids
• reserved words
• null semantics (see below)
• quotation semantics (single quote ', double quote ", or either)
• statement completion semantics
• function semantics
• date handling (including constant keywords like 'now' and input / output function formats)
• whether inline comments are permitted
• maximum attribute lengths
• maximum number of attributes
• connection semantics / security paradigm.

Without boring you on all the conversion data, here's a sample for one datatype, lvarchar:

oracle=varchar(%x) sybase=text db2="long varchar" informix=lvarchar postgres=varchar(%x) ants=varchar(%x) ingres=varchar(%x,%y)

The biggest deal of all, in my view, is null handling; Oracle SILENTLY converts blank input strings to null values. ...Somewhere, a LONG time ago, I read a writeup someone had done about "The Seventeen Meanings of Null" or some such and the real point is that nulls are very valuable and the distinction between a null string and an empty string is useful and non-trivial! I think Oracle made a huge mistake on this one; none of the others have this behavior (that I've ever seen).

My second least favorite was ANTS because unlike all the others, they ENFORCED the silly rules for perfect syntax that absolutely no one else does and while they may be the only DB company to provide perfect adherence to the standard, they are also a royal pain in the butt to write code for.

Far and away my favorite is Postgres; it's very fast in _real_world_ situations, has great support, and is open source / free.

Answer 2

The differences between different SQL Implementations are big, at least under the hood. This boards wont suffice to count them all.

If you have to ask, you also have to ask yourself whether you are in the position to reach a valid and founded decision on the matter.

A comparison von MYSQL and Postgres can be found here

Note that Oracle offers also an Express (XE) edition, reduced in features, but free to use. Also, if you have little knowledge to start with, you will have to learn yourself, I would just choose any one, and start learning by using it.

Answer 3

See the comparison tables on wikipedia: http://en.wikipedia.org/wiki/Comparison_of_object-relational_database_management_systems && http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

Oracle may or may not be the best. It's expensive, but that doesn't mean best.

Have you looked at DB2? Sybase? Teradata? MS SQL?