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

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.
Duplicating most of a table into an index is always going to be a loser
in the end because of the redundant storage.

regards, tom lane