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

Ok, thanks, Jeff.
This is not a critical problem. Just annoying. I'll wait for 8.2.
BTW, while I can see the reason for adding the IF EXISTS clause to the
language for checking the existence of objects, wouldn't it be easier to
simply provide the PL/PgSQL language for script loading? Then it would be
possible to create scripts for database maintainance that could be run
periodically without having to make them functions stored in the database.
Just a thought.
Cheers!

Jeff Davis-8 wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
View this message in context: http://www.nabble.com/IF-EXISTS-tf2308139.html#a6418758
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Wed, Sep 20, 2006 at 04:12:16PM -0700, Jeff Davis wrote:

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.

Hurra !

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On 9/21/06, stevethames <steve@softlife.com> wrote:

Ok, thanks, Jeff.
This is not a critical problem. Just annoying. I'll wait for 8.2.
BTW, while I can see the reason for adding the IF EXISTS clause to the
language for checking the existence of objects, wouldn't it be easier to
simply provide the PL/PgSQL language for script loading? Then it would be
possible to create scripts for database maintainance that could be run
periodically without having to make them functions stored in the database.
Just a thought.

Or, more importantly, be able to commit within a procedure, i.e.
make a LOOP which will COMMIT every 1000th iteration. Would
be great for scripting maintenance activities.

Regards,
Dawid