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.
