Error messages are your friend (postgres is good)

Here is a bit of simple (yet subtly-flawed) sql, which produces different answers on different database engines:

0 dkg@pip:~$ cat test.sql
drop table if exists foo;
create table foo (x int, y int);
insert into foo VALUES (1,3);
insert into foo VALUES (1,5);
select y from foo group by x;
0 dkg@pip:~$ sqlite3 < test.sql
5
0 dkg@pip:~$ mysql -N dkg < test.sql
3
0 dkg@pip:~$ psql -qtA dkg < test.sql
ERROR:  column "foo.y" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select y from foo group by x;
               ^
0 dkg@pip:~$ 

• Clear error reporting and
• an insistence on explicit disambiguation

are two of the many reasons postgresql is my database engine of choice.

Tags: errors, postgresql, sql

Syndicated 2012-12-04 20:32:00 from Weblogs for dkg