Archive for the ‘PostgreSQL’ category

DROP TABLE IF EXISTS in PostgreSQL

January 27th, 2007

I did the first version of Klebran in December, and loaded it up onto its website, but I haven’t announced it yet, because I’m still working on it. One of the issues for a webapp is having multiple users use it simultaneously – in Konjugator that wasn’t such a problem, since I just filled a working table with the generated forms, printed them to screen, and emptied the table. But with Klebran, the text and checking data has to be maintained in a table, so that users can redisplay it when they make corrections. With the first version, the text imported last was the text that everyone else saw, even if they’d just imported other text! Not good …

I thought CREATE TEMPORARY TABLE would do the trick – this creates a table per user per session, and deletes it once the user has finished with it. But (probably because of my limited PostgreSQL/PHP skills!) it wouldn’t carry across pages – on the import page everything was dandy, but once we moved on to the display page, the handle to the table was lost. So I decided to do something slightly different – create the table with the user’s IP address appended to the name. That should cover most instances, especially in office/school use where each machine will have a different address. The only drawback is that I will need to vacuum these tables automatically at intervals.

However, there is then a problem. If the table does not exist, Klebran will create it on first use. But if the user tries to input a new piece of text, he will get an error message that the table could not be created because it already exists. OK – add a DROP TABLE command first. That deals with subsequent uses, but now he will get an error message on first use, saying that the table cannot be dropped because it does not exist! Catch-22 …

What we need is a conditional drop, like MySQL’s DROP TABLE IF EXISTS, where the table will be dropped if it exists, ready for initial creation or re-creation as appropriate. The latest version of PostgreSQL (8.2) contains such a command, but earlier versions don’t, and my working version of openSUSE is using one of those. It took quite a bit of searching to get a way around this, but I eventually found the “Ron Johnson option”. A query like:
select count(*) from pg_class where relname = ‘mytable’
will return 1 if mytable exists, and 0 if it doesn’t, so you can run a DROP TABLE command (or not) based on the returned value. Perfect!

I moved from MySQL after its daft decisions on how to treat encoding, and its botched initial implementation. I have found PostgreSQL a much more pleasant DB to work with (to my surprise), but I feel that I am still scratching the surface as regards understanding its capabilities. Obviously, it has commands to do whatever you need to – it’s finding them that is the problem for newbies. So it’s nice that progress is being made in the direction of making PostgreSQL’s functionality more accessible.