Postgres SQL Syntax (Was Re: [rt-users] Upgrade problems)

[Les Driggers]

On Fri, 2005-01-28 at 09:15 -0500, Jesse Vincent wrote:
> > 
> > It dies with an error to the effect of 'index ticketcustomfieldvalues1
> > doesn't exist'. So, the rest of the commands in that file fail, thus
> > some critical DB updates don't happen.
> > 
> > Removing the following two lines; 
> >   drop index ticketcustomfieldvalues1;
> >   drop index ticketcustomfieldvalues2;
> > from etc/upgrade/3.3.0/schema.Pg, allows it to complete successfully and
> > the history displays properly.
> > 
> > Is this a problem with my existing database missing these indexes or
> > does the 3.3.0/schema.Pg file need to be modified in the next RT
> > release?
> 
> I'd thought we'd already caught that. Hm. perhaps only for mysql. Is
> there a postgres syntax for "DROP index IF EXISTS?


I don't know. I'm not a SQL guru by any stretch, but from the docs;

Synopsis
DROP INDEX name [, ...] [ CASCADE | RESTRICT ]
Description
DROP INDEX drops an existing index from the database system. To execute
this command you must be the owner of the index. 

Parameters
name
        
        The name (optionally schema-qualified) of an index to remove. 
        
CASCADE
        
        Automatically drop objects that depend on the index. 
        
RESTRICT
        
        Refuse to drop the index if any objects depend on it. This is
        the default. 
        
        
I don't know what schema-qualified means or if it would help, but that
is all I see that offers hope. Could 'name' be derived from a select
statement?

Thanks,
Les