From: | legrand legrand <legrand_legrand(at)hotmail(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Is there a work around for partition key needed for constraint |
Date: | 2018-10-25 21:02:46 |
Message-ID: | 1540501366915-0.post@n3.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jehan-Guillaume (ioguix) de Rorthais | 2018-10-25 21:16:55 | Re: Is there a work around for partition key needed for constraint |
Previous Message | Johannes Truschnigg | 2018-10-25 18:07:30 | Re: upgrade slave in streaming replication from PG 10.4 to 10.5.1 |