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)

closed as too broad by RolandoMySQLDBA, ypercubeᵀᴹ, Paul White, dezso, Marian Mar 6 '14 at 16:05

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

11

(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.

  • 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.