PostgreSQL Upsert not working on Partitioned Table

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.

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.