Squirrel Hacker

Almost all last week at work I was working on adding Postgres to the database interfacing part of our php framework. The more I worked on it and did research to figure out why things were the way that they were and why certain things didn’t work the same way as I thought, because of my limited knowledge of databases other than MySQL and a bit of Oracle, they should, the more that I liked this database backend (side note: why doesn’t chrome have an add to dictionary option when right click on something you ‘misspelled’?). It is not that one is definitively better than the other, it is just a different way of tackling the same problems (well… in my case they are pretty much the same problems…).

For the most part, simple queries that you would write are the exact same:

SELECT * FROM my_table WHERE my_column = 'something'

This will of course return all of the things in the database where my_column = ’something’, nice and simple.

Problems sometimes arise however when you want to do more complex things. For example if you want to delete everything from two tables where one column is the same as the other:

In MySQL, easy:

DELETE table1.*, table2.* FROM table1, table2 WHERE table1.column1 = table2.column1

In Postgres, it is easy as well, but different:

(Downloading and installing Postgres so I don’t mess this up… that would be embarrassing…)

CREATE TEMPORARY TABLE mytmptable(column1 text);
INSERT INTO mytemptable(SELECT table1.column1 FROM table1, table2 WHERE table1.column1 = table2.column1);
DELETE FROM table1 WHERE table1.column1 IN (SELECT * FROM mytemptable);
DELETE FROM table2 WHERE table2.column1 IN (SELECT * FROM mytemptable);

You could do the delete in a similar way to that in MySQL, but you probably wouldn’t because the other way is simpler. It is more dangerous though because of the possibility to delete everything in your tables while you are testing by accident if you miswrite the query.

Fortunately not everything is more complicated in Postgres, for example you can do:

DELETE FROM table1 WHERE column1 IN (SELECT column1 FROM table2 WHERE column1 LIKE 'some text' LIMIT 10 OFFSET 2)

and it will have no trouble deleting things from your tables. In MySQL you cannot do this because of the way that MySQL queries the tables when it uses LIMIT and OFFSET (apparently). What you have to do is this:

CREATE TEMPORARY TABLE mytemptable(column1 text);
INSERT INTO mytemptable(SELECT column1 FROM table2 WHERE column1 LIKE 'some text' LIMIT 10 OFFSET 2);
DELETE FROM table1 WHERE column1 IN (SELECT column1 FROM mytemptable)

This makes postgres a more attractive option to me… if only it supported

CREATE TABLE IF NOT EXISTS ...

…

As you should know, Chrome was released last night at 12 am GMT. It was announced 2 days ago much to the surprise of most (though there were rumours, there was no actual proof of its existence). Many people have now taken the plunge, downloaded it, and tried it out.

My experiences have been almost completely without problem. There was a small problem on grooveshark.com where I could not use the spacebar in flash, but that could have been flash acting up instead of Chrome. I just tried it out again, I think it is Chrome, good thing there is a bug report link built in so I don’t forget to report it this time.

Oh ya, and for websites that use a lot of javascript, there currently is nothing faster (could be because you [I] have extensions for firefox running, which need to be processed as the pages load). Oh… and the Wordpress backend is wicked fast now (thanks to Chromes speed and the new “Turbo” feature that uses google gears incorporated into Wordpress’ backend).

Sorry Gavin and co, it is only available for Windows for now