Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question

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

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

9  
Postgres has much more advanced SQL features than MySQL (CTEs, Window functions, recursive queries, deferrable constraints and many more), here is an overview sql-workbench.net/dbms_comparison.html –  a_horse_with_no_name Mar 5 at 22:07

1 Answer 1

up vote 9 down vote accepted

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

share|improve this answer
    
Add CHECK constraints and DESC indexes and filtered indexes and computed columns (the MariaDB variant has them only). –  ypercube Mar 6 at 9:15
    
@ypercube Wow, really? Yep, verified. That's ... sad. –  Craig Ringer Mar 6 at 9:17
    
And deferred constraints. –  ypercube Mar 6 at 9:17
    
Things that MySQL has (but not Postgres) are clustered indexes, GROUP BY .. ROLLUP and the Event (scheduler). –  ypercube Mar 6 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 at 11:02

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