Date: Thu, 25 Oct 2018 14:02:46 -0700 (MST)
From: legrand legrand <legrand_legrand@hotmail.com>
To: pgsql-admin@postgresql.org
Message-ID: <1540501366915-0.post@n3.nabble.com>
In-Reply-To: 
 <CACut7uS5pRV1=Y-p2q2-udF8BB8xjf4SaHj0BkYWDLBbM6yeRA@mail.gmail.com>
References: 
 <CACut7uTMN=E-OK+gdvUU3BMn+O+OAbA9nd3R=W9wLfnvv2TaHg@mail.gmail.com>
 <1540407880009-0.post@n3.nabble.com>
 <CACut7uS5pRV1=Y-p2q2-udF8BB8xjf4SaHj0BkYWDLBbM6yeRA@mail.gmail.com>
Subject: Re: Is there a work around for partition key needed for constraint
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

Maybe there is a solution equivalent to ON CONFLICT DO NOTHING,
with some BEFORE INSERT TRIGGERS returning NULL when row already exists.

Something like:

create table tabpart (account_id integer not null, customer_id integer not
null, date_added date ) partition by list (date_added);
create table tabpart1  partition of tabpart for values in ('2018-10-24');
create table tabpart2  partition of tabpart for values in ('2018-10-25');
...
create index tabpart_index_id on tabpart(account_id,customer_id);

CREATE OR REPLACE FUNCTION f_check_pkey() RETURNS TRIGGER AS $$
	BEGIN
	  IF count(1) >= 1 FROM tabpart WHERE account_id  = NEW.account_id 
                                   AND customer_id = NEW.customer_id 
          THEN
	        RETURN NULL;
	  ELSE
		RETURN NEW;
	  END IF;
	END;
$$ LANGUAGE plpgsql ;

applied for each partition
CREATE TRIGGER check_pkey BEFORE INSERT ON tabpart1
     FOR EACH ROW EXECUTE PROCEDURE public.f_check_pkey();

CREATE TRIGGER check_pkey BEFORE INSERT ON tabpart2
     FOR EACH ROW EXECUTE PROCEDURE public.f_check_pkey();

...

postgres=# insert into tabpart values (1,1,'2018-10-24');
INSERT 0 1
postgres=# insert into tabpart values (1,1,'2018-10-24');
INSERT 0 0
postgres=# insert into tabpart values (1,1,'2018-10-25');
INSERT 0 0


If you want to be informed of Duplicated rows you can
add :
	  IF count(1) >= 1 FROM tabpart WHERE account_id  = NEW.account_id 
                                   AND customer_id = NEW.customer_id 
          THEN
		RAISE NOTICE 'duplicate key value violates unique constraint "%" ON "%"', 
		  TG_NAME, TG_TABLE_NAME 
		  USING DETAIL = format('Key (account_id,customer_id)=(%s,%s) already
exists.',NEW.account_id, NEW.customer_id);
	        RETURN NULL;
	  ELSE


--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html