From: Rui DeSousa <rui.desousa@icloud.com>
To: Marco Piovan <mpiovan@workwave.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: set not null column using existing constraint
Date: Wed, 10 Jan 2018 11:00:03 -0500
Message-ID: <45C1CF20-054D-4AD2-8886-76F499A39285@icloud.com> (raw)
In-Reply-To: <CAKGTZp7V9=Zuqum5fam0wBe0n1e6TVu+8MLf03Fo-y1JaVMLzw@mail.gmail.com>
References: <CAKGTZp7V9=Zuqum5fam0wBe0n1e6TVu+8MLf03Fo-y1JaVMLzw@mail.gmail.com>
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@workwave.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 <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rui.desousa@icloud.com, mpiovan@workwave.com
Subject: Re: set not null column using existing constraint
In-Reply-To: <45C1CF20-054D-4AD2-8886-76F499A39285@icloud.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox