Re: IF EXISTS

On Wed, 2006-09-20 at 14:18 -0700, stevethames wrote:
> I have a script that sets up my databases which I run whenever I change any
> of the functions. It has a number of things it does that are unnecessary
> and cause errors. I create some types, sequences, etc. The error messages
> are irritating. I'd like to do something like this:
>
> IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname='seqid')
> CREATE SEQUENCE SeqID;
>
> This works in other SQL languages like SQLServer and MySQL. Is there
> anything like this in PostgreSQL?
>

In 8.2, which is currently still in production, they have added the
feature where you can do things like:

DROP SEQUENCE IF EXISTS mysequence;
CREATE SEQUENCE mysequence;

Which makes writing SQL scripts much easier. They also made it work for
other objects, like DROP TABLE IF EXISTS, etc.

This might not help you, because 8.2 is still months away from being
"production quality". However, a beta isn't too far off and you may be
interested to check it out. At least you know the code you want is
already written :)

I think the DROP IF EXISTS syntax makes more sense than CREATE IF NOT
EXISTS, because normally the purpose of this type of thing is to reset
your tables or sequences to the starting state.

Regards,
Jeff Davis