This document lists features present in PostgreSQL but not MySQL, or features present in both databases which the PostgreSQL implementation provides greater functionality. It is a condensed version of the document pgsql_mysql.txt, which contains further annotations and links to each database's documentation where relevant. 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. - PostgreSQL offer advanced cursor support as part of its core functionality. 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. - PostgreSQL supports creation of external functions in C and C++ as well as user-defined datatypes for use with external functions. Many mature third-party extensions to PostgreSQL's core functionality have been created using this feature. 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" which can only be used on primary key columns to generate values in increments of 1 and when used with transactions can cause deadlocks. - 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. 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. - PostgreSQL provides stored procedures in SQL, its own procedural language PL/PgSQL and a variety of other languages including Perl, Tcl and Python. 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. Beginning with version 7.4 it will also provide database meta-information via the standardized "information_schema" defined in SQL-92. 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. - 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. Views ----- Views are predefined SELECT statements and can be used to simplifiy complex SELECT statements, restrict user access to underlying tables or present a consistent view of the database to applications. - MySQL does not support views. Implementation is planned at an unspecified future date. - PostgreSQL supports views as part of its core functionality. 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. - PostgreSQL provides four access methods (B-tree, R-tree, hash and GIST) which can be used to optimize retrieval strategies according to need. Foreign Keys ------------ Explicitly defining relationships between tables as foreign keys is an vital element in maintaining data integrity. - In MySQL foreign key support is optionally available as part of the InnoDB extension. Under certain circumstances MySQL may ignore foreign key restraints or correct but unparsed foreign key syntax. - PostgreSQL provides inbuilt and robust foreign key support as part of its core functionality. Full-text indexing ------------------ Databases storing large amounts of textual data require efficient search methods beyond those provided by the LIKE clause in SELECT statements and which provide intelligent handling of natural language syntax. - MySQL provides an integrated index type "FULLTEXT". This enables simple full-text searches using a stopword list in single-byte character sets. Tables with a FULLTEXT index are not transacton-capable. - 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. 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. - 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. 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. - PostgreSQL is available under the BSD licence to both commercial and non-commercial users. No plans exist to change PostgreSQL licencing. 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. - MySQL aims to work towards compliancy with the SQL-99 standard, but will continue to implement non-standard extensions where it sees fit. - PostgreSQL aims at full compliance with the most recent SQL standard where reasonably possible.