Postgres Pro
Downloads
Home   >   mailing lists

Thread: set not null column using existing constraint

set not null column using existing constraint

From
Marco Piovan
Date:
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

Re: set not null column using existing constraint

From
Keith
Date:


On Wed, 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):

"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.

Re: set not null column using existing constraint

From
Rui DeSousa
Date:
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):

"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

Re: set not null column using existing constraint

From
Marco Piovan
Date:
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.desousa@icloud.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@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):

"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@workwave.com | workwave.com

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

​
         

Re: set not null column using existing constraint

From
"David G. Johnston"
Date:
On Wednesday, January 10, 2018, Marco Piovan <mpiovan@workwave.com> wrote:
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.

It probably doesn't exist then, which is not that surprising; no one foresaw people using a check constraint for not null when an explicit specifier exists.

David J.