4 Dec 2012 dkg   » (Master)

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

Latest blog entries     Older blog entries

New Advogato Features

New HTML Parser: The long-awaited libxml2 based HTML parser code is live. It needs further work but already handles most markup better than the original parser.

Keep up with the latest Advogato features by reading the Advogato status blog.

If you're a C programmer with some spare time, take a look at the mod_virgule project page and help us with one of the tasks on the ToDo list!