Thread: [PATCH] Support for ALTER TABLE ADD UNIQUE/PKEY USING INDEX

This may be a little late, but according to the postgres documentation,
isn't an index supposed to be created automatically by postgres when you
create a unique or primary key constraint? So isn't this redundant. Note
that I am not sure because I created a unique constraint in an 8.4 db
using pgadmin *and* via DDL in the sql editor, and an index did not
appear to be created; counter to what the docs say is supposed to
happen. I'd be interested to hear someone's take on this.

http://www.postgresql.org/docs/9.1/interactive/ddl-constraints.html#AEN2470

Regards,

BillR

On Wed, 2012-03-14 at 22:46 -0400, BillR wrote:
> This may be a little late, but according to the postgres documentation,
> isn't an index supposed to be created automatically by postgres when you
> create a unique or primary key constraint?

Yes, it is.

>  So isn't this redundant.

The support for using an already existing index to add this kind of
constraint? not at all, it's actually great. It allows a user to add
this kind of constraints quicker.

>  Note
> that I am not sure because I created a unique constraint in an 8.4 db
> using pgadmin *and* via DDL in the sql editor, and an index did not
> appear to be created; counter to what the docs say is supposed to
> happen. I'd be interested to hear someone's take on this.
>

pgAdmin only shows the constraint. The fact that it's done with an index
is an implementation detail.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Apologies to Guillaume for the duplicate reply, but I didn't hit "reply to all" the first time so didn't actually reply to the list. So here it is again (the first time).

>pgAdmin only shows the constraint. The fact that it's done with an
>index
>is an implementation detail

So creating another index on the column with the 'unique' constraint is redundant I take it. It would seem to me that this "implementation detail" is unnecessarily obscure/confusing. But that is with postgresql, not pgamin. To add to my first reply, I have noticed confusion WRT this from others on the web when trying to gain insight searching the web. So I know it's not just me. If creating another index is redundant, then I suspect there are a lot more redundant indexes out there because of the way this is implemented. But again, that is with Postgresql.

Thanks,

BillR
--
Sent from my Android phone with K-9 Mail. Please excuse my brevity.

On Thu, 2012-03-15 at 10:30 -0400, BillR wrote:
> Apologies to Guillaume for the duplicate reply, but I didn't hit
> "reply to all" the first time so didn't actually reply to the list. So
> here it is again (the first time).
>

No problem :)

> >pgAdmin only shows the constraint. The fact that it's done with an
> >index
> >is an implementation detail
>
> So creating another index on the column with the 'unique' constraint
> is redundant I take it.

Yes.

>  It would seem to me that this "implementation detail" is
> unnecessarily obscure/confusing. But that is with postgresql, not
> pgamin.

Exactly.

>  To add to my first reply, I have noticed confusion WRT this from
> others on the web when trying to gain insight searching the web. So I
> know it's not just me. If creating another index is redundant, then I
> suspect there are a lot more redundant indexes out there because of
> the way this is implemented. But again, that is with Postgresql.

Exactly.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com