eSpace Blog RSS
24Feb

MySQL vs PostgreSQL: key differences in queries

0

Coming from a MySQL background, and trying PostgreSQL for the first time, I am experiencing some key differences in query syntax. Some queries that used to work fine under MySQL now produce errors under PostgreSQL. Generally, PostgreSQL's query syntax is tighter, stricter than ANSI SQL. For anyone moving from MySQL to PostgreSQL, it would be useful to know these differences, so I'll post them in groups as I go deeper in PostgreSQL with time.

The first difference I encountered is that aliasing in PostgreSQL requires an explicit 'as'. This means that the following query, which used to work under MySQL, won't work under PostgreSQL:

SELECT count(id) count FROM users;

Instead, an explicit 'as' is needed before the alias:

SELECT count(id) AS count FROM users;

Another difference is the 'group by' issue. In MySQL, queries were allowed to group the results by a subset of, not necessarily all, the selected columns. For example, the following query works under MySQL:

SELECT users.name, users.id, count(telephones.id)
 FROM users LEFT JOIN telephones
 ON telephones.user_id = users.id
 GROUP BY users.id;

Such a query won't work under PostgreSQL. When grouping, all selected columns (except aggregated ones) must appear in the group-by clause:

SELECT users.name, users.id, count(telephones.id)
 FROM users LEFT JOIN telephones
 ON telephones.user_id = users.id
 GROUP BY users.id, users.name;

This limitation makes it hard to build a query to return results that are distinctly based on specific columns, i.e. this way I can't use 'group by' to return distinct results based on users.id only.
However, PostgreSQL comes with a good feature that will help you return distinct results based on some of - not all - the selected columns; 'distinct on' can be used as follows:

SELECT DISTINCT ON (users.id) users.name, users.id
 FROM users LEFT JOIN telephones
 ON telephones.user_id = users.id;

Nevertheless, It is worthwhile to point out that distinct-on clauses can't be used with order-by clauses in case the columns list in both clauses are different, i.e. the following query won't work because the distinct-on columns list is different than the order-by colmns list:

SELECT DISTINCT ON (users.id) users.name, users.id
 FROM users LEFT JOIN telephones
 ON telephones.user_id = users.id
 ORDER BY users.name;

Written By:

Haitham Mohammad (e-haitham.blogspot.com)

Post a Comment

eSpace podcast Prodcast

RSS iTunes

Archive

Latest Comments