Re: CREATE IF NOT EXISTS INDEX

On 09/30/2014 04:58 PM, Fabrízio de Royes Mello wrote:
> On Tue, Sep 30, 2014 at 8:47 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>
>> On 09/30/2014 04:16 PM, Andres Freund wrote:
>>> On 2014-09-30 16:03:01 -0700, Josh Berkus wrote:
>>>> On 09/30/2014 03:53 PM, Andres Freund wrote:
>>>>> Good point. I think it's fair enough to only allow CINE on named
>>>>> indexes.
>>>>
>>>> On the other hand, the way we form system-generated names is
> predicable,
>>>> so I think it would be perfectly OK to include them. Desirable, in
> fact.
>>>
>>> It's not really that predicable. Think about expression indexes. They
>>> also don't contain information about opclasses et all.
>>>
>>> Seems like pit of hairy semantics.
>>>
>>>> Not that I'm a fan of omitting the name ...
>>>
>>> Me neither.
>>
>> I'd be OK with a CINE which required you to name the index.
>>
>> How does your patch work at present, Fabrizio?
>>
>
> My patch will work just if you name the index, because postgres generates a
> index name that doesn't exists.
>
> I don't check to a name if we use IF NOT EXISTS, but I can add this check.

The consensus is that we don't want IF NOT EXISTS to work for
automatically generated index names. For that reason, we'd want it to
error out if someone does this:

CREATE INDEX IF NOT EXISTS ON table(col);

My suggestion for the error message:

"IF NOT EXISTS requires that you name the index."

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com