Thread: IF EXISTS
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? -- View this message in context: http://www.nabble.com/IF-EXISTS-tf2308139.html#a6416894 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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
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
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 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