Mysql – PostgreSQL specific SQL keywords

comparison, mysql, postgresql

What are specific features from Postgres that are not available in MySQL?

Are there some queries that you wouldn't be able to do as easily? Or are the differences mostly in how you store your data?

Best Solution

I would say that two of the largest differences are WITH queries and window functions -- standard SQL features (from the SQL-99 standard) that are also available in other major SQL implementations (such as Oracle, DB2, SQL Server, ...), but not in MySQL.

Many minor things of course, e.g.:

MySQL has some non-standard conveniences, such as INSERT IGNORE and REPLACE, which PostgreSQL lacks. PostgreSQL's stored procedures and triggers can use any of several languages (such as Python, Java, Perl, ...), MySQL's (like DB2's) use the SQL'03 standard syntax here.

Also outside the standard, PostgreSQL has many peculiar data types (including user-defined types and multi-dimensional arrays), MySQL has unsigned integers.