Pro:
The most sophisticated of all the open source RDBMS. Has declarative referential integrity constraints, triggers, stored procedures, mature transaction support, multi-versioning concurrency control (MVCC) and write-ahead logging for safety and excellent performance under load.
More SQL-92 compliant than most RDBMS, including Oracle.
Stored procedures can be written in PL/pgSQL (a PL/SQL workalike), PL/Tcl, PL/Perl, PL/Python (in 7.2).
Very easy to port between Oracle and PostgreSQL. Makes it easier to migrate to Oracle if the customer wants it.
Supports hot backups, OLAP and ad-hoc queries easily since use of MVCC means that readers never block writers, and writers never block readers. This is also the reason why PostgreSQL and Oracle scale so well. <nop>InterBase has MVCC, but has some internal design issues that cripple its performance.
Well documented. No need to accumulate 1.5 ft of manuals and still wonder how to do/manage something, like Oracle.
Con:
Does not seem to have as widespread support amongst web developers, probably because of the nonsense <nop>NuSphere puts out regarding need for transactions, declarative referential integrity, etc. They are backpeddling on transactions now though, and might eventually acknowledge that the last 30 years of research into RDBMS systems is actually right.
My perception on the lack of web developer support goes like this: Virtual web host providers typically provide MySQL, but not PostgreSQL because the former requires less CPU cycles (in fact some providers have just outright said this), so many people using such hosts learned MySQL because that was _the_ SQL database available. Also, as a MySQL user (for the reason just stated) I have never had any problems in _my_ projects and so never find the time to convert to Postgres among my list of TO DOs. -- ChuckEsterbrook - 31 Dec 2001
I don't think it's an anti-PostgreSQL bias, just the ordinary hurdle of not being the first, like wxWindows trying to bust into the Tkinter scene. Many MySQL developers (like me) want to switch to it eventually (because it's "better" -- more features, more robust, better OO, etc), but it's going against inertia. I can add a MySQL database like that <snaps fingers>, but every time I browse the PostgreSQL manual I think, "Aargh! <recoil in horror> I don't have time to read all this, choose among PostgreSQL's many column types, or translate my queries! Maybe later." -- Main.MikeOrr - 31 Dec 2001
Does not have as many third party books and software tools as MySQL. -- ChuckEsterbrook - 31 Dec 2001
OTOH, you don't need anything except the excellent manuals if you already know about RDBMS. If you don't, Bruce Momjian's book is a good starter, and is freely downloadable at: http://www.ca.postgresql.org/docs/awbook.html . There's also Practical PostgreSQL which is also available at: http://www.postgresql.info/ . Frankly though, I find that the online manuals are more current since PostgreSQL is developing so quickly. For third-party tools, well anything that can handle SQL/ODBC will work. Crystal Reports, MS Excel, MS Access, etc. Just use the ODBC driver. -- EdmundLian - 31 Dec 2001
Well in that case, can you recommend a GUI client that works with Postgres that is as nice as: http://anse.de/mysqlfront/ , and a tool like: http://www.new.ox.ac.uk/~adam/computing/mysqldiff/ ? I would have a hard time migrating to Postgres without the latter. -- ChuckEsterbrook - 31 Dec 2001
There is http://ns.flex.ro/pgaccess/ for graphical management/editing of tables, users, sequences functions, etc. There is also http://gborg.postgresql.org/project/pgmonitor/projdisplay.php for monitoring the backend, and http://pgadmin.postgresql.org/pgadmin2.php?ContentID=1 for the W95/8/NT client, which seems to be very nice and more capable than pgaccess. pg_dump and pg_dumpall will extract a database into a script file, which you can then diff. People use it for moving databases and all the associated functions, etc. to another installation. For most stuff, I actually just use psql (http://psql.sourceforge.net/), the terminal-based front-end since I can script it. -- EdmundLian - 31 Dec 2001
-- EdmundLian 31 Dec 2001
I have outstanding questions regarding the so-called "object oriented" features of PostgreSQL. I created PostgresOOFeatures for this.
-- ChuckEsterbrook - 31 Dec 2001