PostgreSQL Upsert Not Working on Partitioned Table – Solutions

plpgsqlpostgresqlupsert

Have a table like this:

CREATE TABLE aggregated_master (
  "user"       BIGINT,
  type         TEXT,
  date         TIMESTAMP,
  operations   BIGINT,
  amount       NUMERIC,
  PRIMARY KEY ( "user", type, date )
);

This table is the master from which a lot of partitions inherit.
The partitions are done by MONTH in the DATE field.
For example: Partition for Aug-2017 would be agg_201708 and it's PK would be pk_agg_201708
There is the usual trigger BEFORE INSERT to redirect the insert to the proper partition.

The thing is that I want to do an UPSERT into this table.
The DO CONFLICT part is not working.

The code first was like this

INSERT INTO aggregated_master (user, type, date, oeprations, amount)
SELECT user, type, date, SUM(ops), SUM(amt)
FROM ...
WHERE ...
GROUP BY USER, TYPE, DATE
ON CONFLICT ON CONSTRAINT pk_aggregated
DO UPDATE SET operations = EXCLUDED.operations
          ,   amount = EXCLUDED.amount

But then I noticed that the constraint (pk_aggregated) is the one on the master table, and not on the child table where the insert will really be performed, due to the trigger.

I changed the clause CONFLICT to:

ON CONFLICT (user, type, date)

Which are the fields of the PK, but this doesn't work either.

Any idea how to make this work ?

Best Answer

PostgreSQL 11 supports INSERT INTO ... ON CONFLICT with partitioned tables:

CREATE TABLE o(id INT PRIMARY KEY, i INT) PARTITION BY RANGE (id);

CREATE TABLE o1 PARTITION OF o FOR VALUES FROM (1) TO (1000);
CREATE TABLE o2 PARTITION OF o FOR VALUES FROM (1000) TO (2000);

INSERT INTO o(id, i) VALUES (1,1),(2,2),(1500,1500);

INSERT INTO o(id, i)
VALUES (1500, 1400), (2,20), (3, 3)
ON CONFLICT (id)
DO UPDATE SET i = EXCLUDED.i;

SELECT * FROM o;

DBFiddle Demo

Limitation ddl-partitioning

5.10.2.3. Limitations

Using the ON CONFLICT clause with partitioned tables will cause an error, because unique or exclusion constraints can only be created on individual partitions. There is no support for enforcing uniqueness (or an exclusion constraint) across an entire partitioning hierarchy.

has been lifted.

Related Question