Re: Setting a pre-existing index as a primary key

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> I realize most feel we don't need to add a rename to this, but there are
> two more reasons _not_ to do this.

One other thought I had about this is that the proposed syntax

ALTER TABLE tab ADD PRIMARY KEY (col [, ...]) USING INDEX foo

is not well chosen anyway. It forces the user to provide a column name
list matching the index, which is just extra typing and extra cognitive
burden, and it forces the system to have code checking that this list
matches the specified index. So I'm thinking it should look like

ALTER TABLE tab ADD PRIMARY KEY USING INDEX foo
or maybe just
ALTER TABLE tab ADD PRIMARY KEY USING foo

This would be a separate grammar production having nothing to do with
the ADD CONSTRAINT syntax. It's not ambiguous since the column name
list is required in ADD CONSTRAINT.

BTW, aside from selecting the index the command would have to verify
that the indexed columns are all NOT NULL. We could either have it
just throw an error if they aren't, or have it silently try to do
an ALTER SET NOT NULL, which would require a table scan.

I'm going to argue for the "just throw an error" choice. I don't like
the idea of a utility command that takes exclusive lock and then is
either near-instantaneous or slow depending on factors not immediately
obvious.

regards, tom lane