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 ?