Re: set not null column using existing constraint

The column is not marked as NOT NULL but a valid constraint "CHECK (column
IS NOT NULL)" exists.

I haven't found a way to use that specific constraint and avoid the table
rescan.

Regards

On Wed, Jan 10, 2018 at 5:00 PM, Rui DeSousa <rui(dot)desousa(at)icloud(dot)com> wrote:

> Marco,
>
> Since the column has already been altered to be not null then attaching
> the primary key will not need to rescan the table to mark the column as not
> null again — just use the “using index" clause.
>
> i.e.
>
> alter table rui.member add primary key using index member_pkey;
>
>
> On 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
>
>
>

--
*Marco Piovan*

Senior System Engineer
mpiovan(at)workwave(dot)com | workwave.com

Via Luigi Da Porto, 2/C - 37122 Verona (VR) - Italy

​
<https://www.workwave.com/> <https://www.facebook.com/WorkWaveCo>
<https://www.linkedin.com/company/workwave> <https://twitter.com/WorkWave>
<https://plus.google.com/u/0/108362178327115857089/posts>