Re: set not null column using existing constraint

On Wed, Jan 10, 2018 at 7:09 AM, Marco Piovan <mpiovan(at)workwave(dot)com> wrote:

> Hello,
> I want to create a primary key using an existing unique index.
> I have already created the index concurrently and I have already validated
> a not null constraint on the same column.
>
> Looking at the postgres documentation (https://www.postgresql.org/
> docs/9.6/static/sql-altertable.html):
>
> "If PRIMARY KEY is specified, and the index's columns are not already
> marked NOT NULL, then this command will attempt to do ALTER COLUMN SET
> NOT NULL against each such column. That requires a full table scan to
> verify the column(s) contain no nulls. In all other cases, this is a fast
> operation."
>
> Is it possible to specify the already validated not null constraint and
> avoid another scan of the table (with lock)?
>
> Best regards
>
> --
> Marco Piovan
>

As the docs say, If the NOT NULL constraint exists on the given column and
is valid, it will not do the full table scan. Just use the USING clause to
the command to set the primary key giving it the unique index you already
made.

ALTER TABLE ... ADD PRIMARY KEY USING unique_index;

No need to specify the constraint.