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 1l4BvU-00033y-F3 for pgsql-admin@arkaria.postgresql.org; Tue, 26 Jan 2021 00:09:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l4BvS-00054N-DN for pgsql-admin@arkaria.postgresql.org; Tue, 26 Jan 2021 00:09:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l4BvS-00054G-2E for pgsql-admin@lists.postgresql.org; Tue, 26 Jan 2021 00:09:42 +0000 Received: from mail-ot1-x32f.google.com ([2607:f8b0:4864:20::32f]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1l4BvL-0007zg-DX for pgsql-admin@lists.postgresql.org; Tue, 26 Jan 2021 00:09:41 +0000 Received: by mail-ot1-x32f.google.com with SMTP id f6so14596840ots.9 for ; Mon, 25 Jan 2021 16:09:35 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=J+dplll3E2m26PhxNfofjog7ecS6qfUszJNXBNyWiBE=; b=fxT0irYW67pgxMS1fatWlu6YK5RS51IvlkvgIlkL1Lz+mOVQg8QrlQpSTlxeDgoe+q EvVuPt71T86Wt8G3PRgja1U9MTcp3Y0oY+CLDaNepmv5d66lUAX7xsZCDzTGmqf8pwxm r0pRvOTVA7pvJA2FCGaP0hnz1/beZO0SnxC23GA2grMqh1PAwzLztyA619DQs5SbGzzZ DtxXagJiW4ZYHxnVer/YETi0JiHETwBZ/k0kJgT653sBtD2wO+CErSZn7JgQeutiAYK4 mhxTKHu1Jr1/YNJXyp2fT7VAX+gk4jyFeNyemqb2N5UISf40rHcGZ3Eyn3kytu+v0vCj 0png== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=J+dplll3E2m26PhxNfofjog7ecS6qfUszJNXBNyWiBE=; b=CJQTzkB+1eIf5oIpfmoEEOnTYNS4cBtZ1i9DY6iHfCTlNe38JpkpRX/ae/SWCMaDYU l6tdOtz71gFaSPYzW1KScnNm6+UlvCipfMCYSCM7Xzmxq++NsPCGch3m++tDl6ngiXKw glDiaShTuVyK0VqRlhwxLfGLQ6NMv3Wecg0tlmMVzUg8uxVaWQDDl+SHXyd1Dkaj5j/Z oVUSxkcLEdFHuvsOmkn+F9d7DobJb7DzTToTCzJDk7mIeYIdrwbmVlu30o3698w676S/ 3l7kZL6uDcEESWe6LHpt60O/BNz6Mzz+/Tk6sglJD2aEgF7d+SbdLxi/sqbfrbSpxTfO Szag== X-Gm-Message-State: AOAM532JO4STjCkdzC6PHrmdDwjBbVmUIyyRLjCGqq7fUiJP7MkzmoQr N0nDp6NsecKoi+c61HPrZXz4n4/v/+fFbg== X-Google-Smtp-Source: ABdhPJwGw1+ueLpLNDzgLMTc69hhyKm73pz76f31758SLl2GgEREDjxf+R0CY8FGdvDEAOpGW2gS5w== X-Received: by 2002:a05:6830:1545:: with SMTP id l5mr2236764otp.61.1611619774177; Mon, 25 Jan 2021 16:09:34 -0800 (PST) Received: from [192.168.88.10] (ip174-67-141-204.no.no.cox.net. [174.67.141.204]) by smtp.googlemail.com with ESMTPSA id w66sm2943242oif.21.2021.01.25.16.09.33 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 25 Jan 2021 16:09:33 -0800 (PST) Subject: Re: Unique constraint across all partitions? To: Pgsql-admin References: <3d00c1f4-646c-59b7-e4dc-1d51190d2139@gmail.com> From: Ron Message-ID: Date: Mon, 25 Jan 2021 18:09:32 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------5F0819E10236BA28410CC33C" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------5F0819E10236BA28410CC33C Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 1/25/21 6:01 PM, David G. Johnston wrote: > On Mon, Jan 25, 2021 at 4:02 PM Ron > wrote: > > On 1/25/21 4:44 PM, S Bob wrote: > > > Is it possible to enforce a unique constraint across all partitions for > > either declarative or inheritance based partitioning? Preferably > > declarative based. > > You can create unique indexes on parent tables.  Or am I > misunderstanding you? > > > You seem to misunderstand how partitions and unique constraints work. > > Each partition gets its own copy of the unique index/constraint and > ensures that duplicates do not appear therein.  If the specification for > the unique constraint is something like "id bigint" then the value 5 can > appear on multiple partitions just fine - the partition key(s) effectively > become part of the unique constraint/index even though the values are not > actually stored in the index. > > So, yes, so long as your unique constraint takes into consideration the > partitioning scheme it will enforce uniqueness across all of the > partitions.  But the answer to the full, but possibly implied, question is no. This is why global indices are needed. -- Angular momentum makes the world go 'round. --------------5F0819E10236BA28410CC33C Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 1/25/21 6:01 PM, David G. Johnston wrote:
On Mon, Jan 25, 2021 at 4:02 PM Ron <ronljohnsonjr@gmail.com> wrote:
On 1/25/21 4:44 PM, S Bob wrote:

> Is it possible to enforce a unique constraint across all partitions for
> either declarative or inheritance based partitioning? Preferably
> declarative based.

You can create unique indexes on parent tables.  Or am I misunderstanding you?

You seem to misunderstand how partitions and unique constraints work.

Each partition gets its own copy of the unique index/constraint and ensures that duplicates do not appear therein.  If the specification for the unique constraint is something like "id bigint" then the value 5 can appear on multiple partitions just fine - the partition key(s) effectively become part of the unique constraint/index even though the values are not actually stored in the index.

So, yes, so long as your unique constraint takes into consideration the partitioning scheme it will enforce uniqueness across all of the partitions.  But the answer to the full, but possibly implied, question is no.

This is why global indices are needed.

--
Angular momentum makes the world go 'round.
--------------5F0819E10236BA28410CC33C--