Date: Sun, 11 Jul 2021 19:38:46 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Nagaraj Raj <nagaraj.sf@yahoo.com>
Cc: Mike Sofen <msofen@runbox.com>,
	Alvaro Herrera <alvherre@alvh.no-ip.org>,
	pgsql-performance@postgresql.org
Subject: Re: Partition column should be part of PK
Message-ID: <20210712003846.GA2004@telsasoft.com>
References: <20210708221703.GA1301@telsasoft.com>
 <202107082228.obnak7c7mx4n@alvherre.pgsql>
 <080901d7744e$ac0f3d70$042db850$@runbox.com>
 <613808845.2913413.1625801566230@mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <613808845.2913413.1625801566230@mail.yahoo.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210712003846.GA2004%40telsasoft.com>
Precedence: bulk

On Fri, Jul 09, 2021 at 03:32:46AM +0000, Nagaraj Raj wrote:
> My apologies for making confusion with new thread. Yes its same issue related to earlier post.
> I was trying to figure out  how to ensure unique values for columns (billing_account_guid, ban). If i add partition key to constraint , it wont be possible what im looking for.
> My use case as below 
> INSERT INTO t1 SELECT * from t2 ON CONFLICT (billing_account_guid,ban) DO UPDATE SET something…
> 
> Or
> INSERT INTO t1 SELECT * from t2 ON CONFLICT constraint (pk or uk)(billing_account_guid,ban) DO UPDATE SET something…

I'm not sure, but did you see the 2nd half of what I wrote in June ?

lightly edited:
> It sounds like you want a unique index on (billing_account_guid, ban) to
> support INSERT ON CONFLICT.  If DO UPDATE SET will never move tuples to a new
> partition, then you could do INSERT ON CONFLICT to a partition rather
> than its parent.
> 
> But it cannot be a unique, "partitioned" index, without including load_dttm.

You could try something that doesn't use a parent/partitioned index (as I was
suggesting).  Otherwise I think you'd have to partition by something else
involving the unique columns, or not use declarative partitioning, or not use
insert on conflict.

Justin

PS, I'm sorry for my own confusion, but happy if people found it amusing.