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
