Re: Add primary key/unique constraint using prefix columns of an index

On Tue, May 22, 2012 at 10:41 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
>> Now that there are index only scans, there is a use case for having a
>> composite index which has the primary key or a unique key as the
>> prefix column(s) but with extra columns after that.  Currently you
>> would also need another index with exactly the primary/unique key,
>> which seems like a waste of storage and maintenance.
>
>> Should there be a way to declare a "unique" index with the unique
>> property applying to a prefix of the indexed columns/expression?  And
>> having that, a way to turn that prefix into a primary key constraint?
>
>> Of course this is easier said then done, but is there some reason for
>> it not to be a to-do item?
>
> Um ... other than it being ugly as sin?  I can't say that I can get
> excited about this concept.  It'd be better to work on index-organized
> tables, which is really more or less what you're wishing for here.

IOT would probably be a nice feature too, but adding one more
strategically chosen column to an index is quite different from adding
every column into the index. At least in the general case.

Cheers,

Jeff