• Do you want to uninstall dreamweaver? Are you sure? Are you sure you are sure? You really want to uninstall it? Last chance… #
• @zoster Seagulls like shiny things #
• #can08 There are some pretty funny comments on here actually #
• #can08 Get in on the discussion, go to http://bombasticsoftware.com/canadianelection/ #
• #can08 Well… now we just have to wait for the masses to find our site #
• #can08 Why isn\’t this showing up in the feed? #
• #can08 Testing 123 #
• #can08 Still working on the search url… its tricky to get the right balance… #
• @SeanJA Reply to myself!! #
• #can08 Either it isn’t updating, or people don’t really care about Canadian politics… #
• #can08 In case you were wondering apparently it is the latter #
• Why did seagulls take my water? Why did they take it? Where is my water? #
• @CanadaElects08 http://canadianelection.bombasticsoftware.com/ is new out there… check it out #
• #can08 I wonder if this works… #
• #can08 One more time… #

No tags

• #can08 Testing 123 #
• Africa does not visit my blog very often… #
• Trying out Conkeror… its pretty neat #

No tags

• Watching Firefly #
• Couldn’t sleep… staying up till tomorrow night. #
• Really liking the new Songbird interface, its like a minimalist iTunes… but not slow… #
• Trying to authenticate using the twitter api and php… so far no good… #
• (without using curl) #
• Testing posting from my new application #
• Success! #
• Testing posting from my new application #
• #can08 This is a test from the app! #
• #can08 Test again #
• Why isn’t $(“.class img”).click working?!? Grrrr… #
• #can08 Test #
• #can08 Ha! I can now post from the app! #

No tags

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 ...

…

No tags

• @gavinblair Mouse over to pause the feed… #
• Wonder what happened to that tall girl… what was her name… Prest? #
• I would vote for McCain, he makes good pizza #
• In the news: McCain is doing some illegal dumping in Canada
  http://www.cbc.ca/money/story/2004/01/02/pizza020104.html #
• For someone who wants to be president, McCain can’t speak too good… #
• McCain is no master debater… sorry… had to be said… #
• Happy birthday gun! #

No tags