Re: BUG #17720: pg_dump creates a dump with primary key that cannot be restored, when specifying 'using index ...'

On Thu, Dec 15, 2022 at 4:54 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Vik Fearing <vik(at)postgresfriends(dot)org> writes:
> > If the point in adding a primary key USING INDEX is to avoid building an
> > index, then this restriction defeats that purpose. We have no ALTER
> > INDEX command to switch or drop the <unique null treatment>.
>
> Well, if you want to build an index to then use as a primary key,
> it's incumbent on you to make the index with the correct properties.
> Do you expect the system to silently fix it for you if the index is
> on the wrong columns?
>
> I might have more sympathy for this argument if the correct <unique null
> treatment> were non-default, but it is not. You'd have had to go out
> of your way to make the index incompatible. That in turn suggests that
> there's a mistake somewhere, so having the system automatically fix
> it for you might just be masking something that would be better dealt
> with manually.
>
> regards, tom lane
>
>
> Can you explain the distinction?
If i build an index nulls distinct/nulls not distinct on a column that will
later be used in an
alter index add constraint ... using index
both indexes can have at least one null.
from that point using an index in the creation of a constraint should not
matter if the index could potentially have one or many nulls.
If the pre version 15 syntax is correct, then the post version 15 syntax
should also be correct.
If one is not valid, then both should not be valid.
Or am i missing something here?

Reiner