PostgreSQL features not present in MySQL ======================================== Cursors ------- Cursors enable clients to control the number of result rows returned from the database by stepping through the result set in user-defined intervals. This is particularly useful when manipulating large result sets. - MySQL has no support for cursors. Implementation is planned at an unspecified future date: http://www.mysql.com/doc/en/TODO_MySQL_5.0.html - PostgreSQL offer advanced cursor support as part of its core functionality. Cursor support has been available since at least version 6.4: http://www.postgresql.org/docs/6.4/static/sql-declare.htm External functions ------------------ Specialized applications may require features not part of a database's core functionality but which rely on direct interaction with the database engine and cannot be implemented using stored procedures. This requires the ability to directly extend a database system with external functions. - MySQL has only rudimentary and undocumented support for external functions written in the C language: http://www.mysql.com/doc/en/Writing_a_procedure.html - PostgreSQL supports creation of external functions in C and C++ as well as user-defined datatypes, operators and aggregates. Many mature third-party extensions to PostgreSQL's core functionality have been created using this feature: http://www.postgresql.org/docs/current/static/extend.html Sequences --------- Databases often require the generation of values in sequential order, e.g. to provide unique values for a table's primary key. - MySQL provided only a very basic sequence generator called "AUTO_INCREMENT". There can only be one AUTO_INCREMENT per table and it can only be used on primary key columns. AUTO_INCREMENT can only generate values in increments of 1 (negative increments are not supported). There is no provision for warpround once the sequence hits the column's maximum value. See: http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html As AUTO_INCREMENT is implemented as SELECT MAX(col) FROM table, it makes transactions prone to deadlocks: http://www.mysql.com/doc/en/InnoDB_auto-increment_column.html - PostgreSQL provides flexible sequence support which can generate values in user-defined increments (both ascending and descending) between specified minimum and maximum values. PostgreSQL sequences do not cause deadlocking in transactions: http://www.postgresql.org/docs/current/static/sql-createsequence.html Stored Procedures ----------------- Stored procedures are blocks of code stored and executed in the database backend. They are typically created in an SQL-like procedural language or a fourth-generation language such as Java or Perl. - MySQL has no support for any form of stored procedure. Implementation is planned at an unspecified future date: http://www.mysql.com/doc/en/ANSI_diff_Triggers.html - PostgreSQL provides mature and tested support for function creation in SQL and the following procedural languages: PL/PgSQL (PostgreSQL's own procedural language, similar to e.g. Oracle's PL/SQL) Perl Python Tcl http://www.postgresql.org/docs/current/static/programmer-pl.html An implementation of PHP as a procedural language is currently under development. http://www.commandprompt.com/entry.lxp?lxpe=260 External functions can also be written in C or C++: http://www.postgresql.org/docs/current/static/xfunc-c.html System Catalog -------------- A system catalog (also known as a "data dictionary") stores meta-information about a database as normal tables and views. This enables a DBA to gain information about database operations and possibly manipulate global database settings. - MySQL does not have a system catalog, although it does provide a seperate "mysql" database for manipulating database user permissions. - PostgreSQL has a system catalog as part of its core functionality: http://www.postgresql.org/docs/current/static/catalogs.html Beginning with version 7.4 it will also provide database meta-information via the standardized "information_schema" defined in SQL-92: http://developer.postgresql.org/docs/postgres/information-schema.html Triggers -------- Triggers are units of code stored in the database which are executed when certain actions are carried out. They automate many routine tasks and can be used to enfore data integrity and business rules. - MySQL does not support triggers. Implementation is planned at an unspecified future date. http://www.mysql.com/doc/en/ANSI_diff_Triggers.html - PostgreSQL supports triggers (and a related facility called 'rules') as part of its core functionality. PostgreSQL triggers can be written in any supported procedural language: http://www.postgresql.org/docs/current/static/sql-createtrigger.html http://www.postgresql.org/docs/current/static/sql-createrule.html PostgreSQL has supported triggers since at least version 6.3: http://www.postgresql.org/docs/6.3/static/c38.htm Views ----- VIEWs are an important element of efficient professional database design, pushing repetitive, often duplicated queries into the backend, increasing the flexibility of an application and reducing the risk of errors. They also enable the encapsulation of table structure, which may change with application development, behind a consistent interface. - MySQL does not support views. Implementation is planned at an unspecified future date: http://www.mysql.com/doc/en/ANSI_diff_Views.html - PostgreSQL supports views as part of its core functionality: http://www.postgresql.org/docs/current/static/sql-createview.html PostgreSQL has supported views since at least version 6.4: http://www.postgresql.org/docs/6.4/static/sql-createview.htm Features present in PostgreSQL and MySQL ======================================== Indexes ------- Indexes are an essential part of every database, providing fast access to columns without having to scan each table row by row. - MySQL provides only one access method (B-tree) for indexes: http://www.mysql.com/doc/en/MySQL_indexes.html - PostgreSQL provides four access methods (B-tree, R-tree, hash and GIST) which can be used to optimize retrieval strategies according to need: http://www.postgresql.org/docs/current/static/sql-createindex.html Foreign Keys ------------ Explicitly defining relationships between tables is an vital element in maintaining data integrity. Foreign keys help achieve this by establishing constraints between keys in different tables, making it impossible e.g. for a table containing - In MySQL foreign key support is optionally available as part of the InnoDB extension: http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html Under certain circumstances MySQL may ignore foreign key restraints even though they are correctly defined: http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html MySQL also parses but silently ignores foreign key syntax in some situations, even on InnoDB tables: http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html - PostgreSQL provides inbuilt and robust foreign key support as part of its core functionality: http://www.postgresql.org/docs/7.3/static/sql-createtable.html Full-text indexing ------------------ Databases storing large amounts of textual data, such as document repositories and website backend require efficient search methods which provide intelligent handling of natural language syntax. Although SQL provides several methods of searching for patterns in text fields (LIKE, regexes), these do not provide sufficient functionality for more complex searches, e.g. as used by search engines. - MySQL provides an integrated index type "FULLTEXT". This enables simple full-text searches, although facilities for parsing natural languages apart from a simple, multilingual stopword list are available. Additionaly, only single-byte character sets can be indexed, and tables with a FULLTEXT index are not transacton-capable: http://www.mysql.com/doc/en/Fulltext_Search.html http://www.mysql.com/doc/en/Fulltext_Restrictions.html - Through PostgreSQL's extensibilty model several third-party full-text indexing methods are available. The most advanced is Tsearch2, which provides advanced full-text searches using multilingual dictionary-based lookups and stemming, and will be integrated into PostgreSQL's core functionality in a future release: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ Transaction support ------------------- Transaction processing is a core element of database systems, enabling units of work to be encapsulated into a single transaction Intermediate states between units of work are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then changes can be rolled back without affecting the rest of the database. - MySQL provides transaction support as an optional extra via the InnoDB extension-Not all table types (e.g. tables defined with a FULLTEXT index) can be declared as InnoDB tables. Transactions on a mix of InnoDB and other table types can lead to transactions which cannot be completely rolled back. See: http://www.mysql.com/doc/en/InnoDB_transaction_model.html - PostgreSQL provides integrated transaction support based on the MultiVersion Concurrency Control (MVCC) model. All database objects as well as data definition statements are transaction-capable: http://www.postgresql.org/docs/current/static/mvcc.html Other considerations ==================== Licencing --------- - MySQL is available either under the GPL licence or a MySQL commercial licence, depending on usage. MySQL licencing policy may be subject to change. See: http://www.mysql.com/doc/en/MySQL_licenses.html - PostgreSQL is available under the BSD licence to both commercial and non-commercial users. No plans exist to change PostgreSQL licencing. See: http://www.postgresql.org/licence.html SQL standards compliance ------------------------ A number of standard definitions for SQL (SQL-89, SQL-92, SQL-99) have been issued by ANSI/ISO. While no database has achieved 100% standards compliancy, it is generally regarded as a worthy target to aim for. Reasons to prefer best-possible standards compliancy : * flexibility: less reliancy on proprietary features means less "lock-in" and easier transition to another database system should this later become necessary. * portability: many applications support more than one database system; greater SQL dialect deviation increases development and maintenance time. * skills: skill sets developed on a less-standard system are not so easily transferable, which disadvantages both developers and employers. * future-proofing: A culture of non-standard extensions increases divergence of SQL dialects, exacerbating the problems described in the previous points. - MySQL aims to work towards compliancy with the SQL-99 standard, but will continue to implement non-standard extensions where it sees fit: "We are not afraid to add extensions to SQL or support for non-SQL features if this greatly increases the usability of MySQL Server for a big part of our users." ( http://www.mysql.com/doc/en/Compatibility.html ) See also: http://www.mysql.com/doc/en/TODO_future.html - PostgreSQL aims at full compliance with the most recent SQL standard where reasonably possible: "PostgreSQL development tends to aim for conformance with the latest official version of the standard where such conformance does not contradict traditional features or common sense." ( http://www.postgresql.org/docs/current/static/features.html ) Supported and unsupported features are listed here: http://www.postgresql.org/docs/current/static/features.html http://www.postgresql.org/docs/current/static/unsupported-features-sql99.html Implementation Details ====================== Many elememnts of SQL supported by MySQL are not implemented as fully as in PostgreSQL or are implemented with certain restrictions or idiosyncracies which may cause unexpected behaviour during development and production, particularly when porting SQL to and from other RDBMS. The following lists some features implemented by PostgreSQL but not MySQL. SELECT: join clauses -------------------- FULL OUTER JOIN - MySQL does not support the FULL OUTER JOIN clause It is listed in a file named "Todo sometime": http://www.mysql.com/doc/en/TODO_sometime.html - PostgreSQL has supported all forms of OUTER JOIN since at least version 7.1: http://www.postgresql.org/docs/7.1/static/sql-select.html UNION - MySQL supports the UNION clause beginning with version 4.0: http://www.mysql.com/doc/en/UNION.html Note that the MySQL UNION implementation contains an interesting "gotcha": if the first SELECT contains literal values, subsequent SELECT results will be truncated to the length of those values: http://www.mysql.com/doc/en/UNION.html - PostgreSQL has supported UNION since at least version 6.5: http://www.postgresql.org/docs/6.5/static/sql-select.htm INTERSECT - MySQL does not support the INTERSECT clause. A "Todo sometime": http://www.mysql.com/doc/en/TODO_sometime.html - PostgreSQL has supported INTERSECT since at least version 6.5 http://www.postgresql.org/docs/6.5/static/sql-select.htm EXCEPT - MySQL does not support the EXCEPT clause. No mention of plans to introduce support were found in the documentation. - PostgreSQL has supported EXCEPT since at least version 6.5 (http://www.postgresql.org/docs/6.5/static/sql-select.htm ) Self-referencing table operations: ---------------------------------- INSERT INTO ... SELECT INSERT INTO ... SELECT ... does not work in MySQL if source and target tables are the same: http://www.mysql.com/doc/en/INSERT_SELECT.html ON UPDATE CASCADE / SET NULL MySQL's implentation of ON UPDATE does not allow recursive updates on the same table: http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html