3

Other than performance and security is there really any difference between MySQL and Postgres like SQL queries, built-in functions, procedures and so on?

I'm asking this because I'm familiar with PHP with MySQL (phpMyAdmin) but not with Postgres (phpPgAdmin)

12

(Regarding PostgreSQL 9.3 and MySQL 5.6, written in 2014; if you're looking at other versions, this may be outdated):

Lots more features.

  • CHECK constraints
  • True SERIALIZABLE isolation
  • Arrays (including index support for arrays)
  • Window functions (lead, lag, row_number, etc)
  • Common table expressions (WITH queries) including recursive CTEs and writeable CTEs
  • The hstore hash type and its index support
  • SQL/XML support
  • json support
  • A rich set of functions and operators for many types
  • Partial and function/expression indexes, including partial unique indexes and unique expression indexes
  • Composite types
  • User defined aggregates and operators
  • Set-returning user defined functions
  • User defined types
  • Transactional DDL (this is wonderful)
  • GIN and GiST indexes for complex indexing needs, including K-nearest-neighbour indexing
  • PostGIS
  • UNLOGGED tables (though MyISAM meets this need in MySQL)

See the comprehensive user manual for details.

However:

  • There are no session variables in PostgreSQL, unlike MySQL. (You can abuse GUCs for this, but not to do the same sort of things you do with MySQL session variables).

  • PostgreSQL has no INSERT ... ON DUPLICATE KEY UPDATE ("upsert")

  • At the moment, PostgreSQL has different replication options than MySQL. MySQL has no point-in-time recovery, but PostgreSQL has no logical replication (yet, we're working on it).

  • GROUP BY ... ROLLUP for summaries of groupings. Doing the same thing in PostgreSQL requires a union, usually over two queries over a CTE.

  • PostgreSQL doesn't have a built-in event scheduler. You generally just use cron or the Windows Task Scheduler; there's also PgAgent from PgAdmin-III.

| improve this answer | |
  • Add CHECK constraints and DESC indexes and filtered indexes and computed columns (the MariaDB variant has them only). – ypercubeᵀᴹ Mar 6 '14 at 9:15
  • @ypercube Wow, really? Yep, verified. That's ... sad. – Craig Ringer Mar 6 '14 at 9:17
  • And deferred constraints. – ypercubeᵀᴹ Mar 6 '14 at 9:17
  • Things that MySQL has (but not Postgres) are clustered indexes, GROUP BY .. ROLLUP and the Event (scheduler). – ypercubeᵀᴹ Mar 6 '14 at 9:23
  • Other not so obvious things: you can't create a view in MySQL with a derived table in the select and you can't use dynamic SQL in functions. – a_horse_with_no_name Mar 6 '14 at 11:02

Not the answer you're looking for? Browse other questions tagged or ask your own question.