Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eZInY-0002rS-5J for pgsql-admin@arkaria.postgresql.org; Wed, 10 Jan 2018 16:00:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eZInX-0003DI-8R for pgsql-admin@arkaria.postgresql.org; Wed, 10 Jan 2018 16:00:15 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eZInW-0003Cz-SA for pgsql-admin@lists.postgresql.org; Wed, 10 Jan 2018 16:00:15 +0000 Received: from mr28p48im-tydg10101201.me.com ([17.110.88.44]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eZInP-0006Nh-Ov for pgsql-admin@postgresql.org; Wed, 10 Jan 2018 16:00:13 +0000 Received: from process-dkim-sign-daemon.mr28p48im-tydg10101201.me.com by mr28p48im-tydg10101201.me.com (Oracle Communications Messaging Server 8.0.1.2.20170607 64bit (built Jun 7 2017)) id <0P2C00C00JZST100@mr28p48im-tydg10101201.me.com> for pgsql-admin@postgresql.org; Wed, 10 Jan 2018 16:00:06 +0000 (GMT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=icloud.com; s=04042017; t=1515600006; bh=piFCyGr/sj5A+DUl4QUeB+kTMl1XDEtvwwpQxXG/FUo=; h=From:Message-id:Content-type:MIME-version:Subject:Date:To; b=Af+WBcXe14ZBOuBp98+oFwFLK47yErcA+fv4V4+wCtER1Fh5gIeJi1igDVuESAOK8 Al9h72fgUZSG6jRQq3VgJ+rD0ZvcmEHBn9iHWEhveC1tk+LiSJE4gIY9yj66nG0jjz ar1vmWZCWxNebZUg+Od1OpvvttwcGXFcJC5GSrf+fLDJ8xYmdRJU/3wjqFCUsaHgcR iVOcYNv31fgRM0MttyjM5bB1rakfWDhHwgxrnP3vkmaPNCDJD+diQ4dPkiTiqANwoc 2/DY8F1+9+S0UzLjlSu3ZbXMQSBqB0f/zX2YraJ/297Jxo510Xhb/Awp3MC+XLJA/x Vbndo22u3KrJA== Received: from icloud.com ([127.0.0.1]) by mr28p48im-tydg10101201.me.com (Oracle Communications Messaging Server 8.0.1.2.20170607 64bit (built Jun 7 2017)) with ESMTPSA id <0P2C00LZSKG3EL20@mr28p48im-tydg10101201.me.com>; Wed, 10 Jan 2018 16:00:05 +0000 (GMT) X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:,, definitions=2018-01-10_08:,, signatures=0 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 clxscore=1011 suspectscore=0 malwarescore=0 phishscore=0 adultscore=0 bulkscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.0.1-1707230000 definitions=main-1801100224 From: Rui DeSousa Message-id: <45C1CF20-054D-4AD2-8886-76F499A39285@icloud.com> Content-type: multipart/alternative; boundary="Apple-Mail=_905B72AE-B364-49AC-8B1E-DB29D972195C" MIME-version: 1.0 (Mac OS X Mail 11.2 \(3445.5.20\)) Subject: Re: set not null column using existing constraint Date: Wed, 10 Jan 2018 11:00:03 -0500 In-reply-to: Cc: pgsql-admin@postgresql.org To: Marco Piovan References: X-Mailer: Apple Mail (2.3445.5.20) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_905B72AE-B364-49AC-8B1E-DB29D972195C Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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 =E2=80=94 just use the =E2=80=9Cusing index" clause. =20 i.e. alter table rui.member add primary key using index member_pkey; > On Jan 10, 2018, at 7:09 AM, Marco Piovan = wrote: >=20 > Hello, > I want to create a primary key using an existing unique index.=20 > I have already created the index concurrently and I have already = validated a not null constraint on the same column. >=20 > Looking at the postgres documentation = (https://www.postgresql.org/docs/9.6/static/sql-altertable.html = ): >=20 > "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." >=20 > Is it possible to specify the already validated not null constraint = and avoid another scan of the table (with lock)? >=20 > Best regards >=20 > --=20 > Marco Piovan --Apple-Mail=_905B72AE-B364-49AC-8B1E-DB29D972195C Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 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 =E2=80=94 just use the =E2=80=9Cusing index" = clause.  

i.e.

alter table rui.member add primary key using index = member_pkey;


= --Apple-Mail=_905B72AE-B364-49AC-8B1E-DB29D972195C--