PostgreSQL Upsert not working on Partitioned Table

Question

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 ?

Answer 1

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.

Answer 2

Upsert on partitioned tables is not implemented in versions earlier than Postgres 11.

In Postgres 9.6:

INSERT statements with ON CONFLICT clauses are unlikely to work as expected, as the ON CONFLICT action is only taken in case of unique violations on the specified target relation, not its child relations.

Declarative partitioning does not resolve the problem, Postgres 10:

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.

Workaround

• create unique indexes ("user", type, date) on all child tables,
• create and use a function for insert/update based on the Example 42.2. Exceptions with UPDATE/INSERT described in the documentation.

In Postgres 11 you can use ON CONFLICT on partitioned tables, see lad2025's answer.