Re: Is there a work around for partition key needed for constraint

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