8

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 ?

5

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.

10
+150

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

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

  • This answer needs to be updated for pg11, which does implement it, as @lad2025 notes. – DB140141 Jan 3 at 14:27
  • @DB140141 - thx, the answer updated. – klin Jan 3 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.