Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kmg9t-0005QJ-4p for pgsql-bugs@arkaria.postgresql.org; Tue, 08 Dec 2020 16:48:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kmg9r-00084C-72 for pgsql-bugs@arkaria.postgresql.org; Tue, 08 Dec 2020 16:48:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kmg9r-000845-0n for pgsql-bugs@lists.postgresql.org; Tue, 08 Dec 2020 16:48:11 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kmg9k-0002oo-Sv for pgsql-bugs@lists.postgresql.org; Tue, 08 Dec 2020 16:48:10 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 0B8Gm1HO3254992; Tue, 8 Dec 2020 11:48:01 -0500 From: Tom Lane To: boekewurm+postgres@gmail.com cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16767: Silent dropping of CONSTRAINT... UNIQUE In-reply-to: <16767-1714a2056ca516d0@postgresql.org> References: <16767-1714a2056ca516d0@postgresql.org> Comments: In-reply-to PG Bug reporting form message dated "Tue, 08 Dec 2020 14:17:36 +0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3254990.1607446081.1@sss.pgh.pa.us> Date: Tue, 08 Dec 2020 11:48:01 -0500 Message-ID: <3254991.1607446081@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk PG Bug reporting form writes: > I've just noticed that equivalent unique constraints that are specified in > the same statement only generate one constraint; Yeah, that's intentional. Per the source code comments: * Scan the index list and remove any redundant index specifications. This * can happen if, for instance, the user writes UNIQUE PRIMARY KEY. A * strict reading of SQL would suggest raising an error instead, but that * strikes me as too anal-retentive. - tgl 2001-02-14 The CREATE TABLE man page does explain this with respect to primary keys: The primary key constraint should name a set of columns that is different from the set of columns named by any unique constraint defined for the same table. (Otherwise, the unique constraint is redundant and will be discarded.) However, I see that there's not similar wording under UNIQUE; that says Each unique table constraint must name a set of columns that is different from the set of columns named by any other unique or primary key constraint defined for the table. (Otherwise it would just be the same constraint listed twice.) That implies that such a constraint is redundant, but it doesn't actually say it in so many words. We should probably use wording more like the PRIMARY KEY text. regards, tom lane