PostgreSQL specific SQL keywords

Question

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?

Answer 1

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.

Answer 2

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

There are many, but most importantly: errors are raised when erroneous data is inserted --- and clients can't disable the sanity checks, as opposed to:

mysql> create table foo (id tinyint not null check id > 100);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into foo values (null), ('abc'), (128), ('1');
Query OK, 4 rows affected, 3 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 2

mysql> select * from foo;
+-----+
| id  |
+-----+
|   0 | 
|   0 | 
| 127 | 
|   1 | 
+-----+
4 rows in set (0.00 sec)

Are there some queries that you wouldn't be able to do as easily?

Complex queries with lots of joins: PostgreSQL's query optimizer is vastly better, and nested loops isn't the only available join algorithm. Also, it can flatten sub-queries in the FROM-part of the query. Those are currently materialized in the stable releases of MySQL.