How to create an updatable Postgres view with rules that allows `INSERT` with `ON CONFLICT` queries?

Question

I've created a postgres view (called metrics) that serves as a proxy for two underlying tables (static_metrics and live_metrics). I've also created a set of rules to allow clients to interact with that view as if it were just a normal table, and without having to know anything about the tables underneath it. My intention is that select statements on the view return data from the two underlying tables, but that inserts, updates, and deletes will change the data in just one of the underlying tables (static_metrics). The exact table/view set up is included below.

My problem is that with this arrangement, I get errors when trying to use "upsert" statements in the form INSERT INTO metrics ... ON CONFLICT ...;. For example, this query:

INSERT INTO metrics (segment_type, segment_id, metric_a, metric_b)
VALUES (1, 12345, 0.5, 0.75)
ON CONFLICT (segment_type, segment_id)
DO UPDATE SET (metric_a, metric_b) = (excluded.metric_a, excluded.metric_b);

I get the following error message:

ERROR: INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules

So finally my question: Is there some way I can rewrite my rules to handle this use case? If not, is there some other way that I can restructure my database so that clients can continue interacting with the metrics object but have all reads return data from two distinct tables? The underlying motivation here is that I don't have control over all clients, so I want this to all work seamlessly without any changes needed on their end.

Here are the exact table, view, and rule definitions:

CREATE TABLE static_metrics (
    id serial PRIMARY KEY,
    segment_type int NOT NULL,
    segment_id int NOT NULL,
    metric_a float,
    metric_b float,
);
CREATE INDEX static_metrics_idx ON static_metrics (segment_type, segment_id);

CREATE TABLE live_metrics (
    id int PRIMARY KEY DEFAULT nextval('static_metrics_id_seq'::regclass),
    segment_type int NOT NULL,
    segment_id int NOT NULL,
    metric_a float,
    metric_b float,
);
CREATE INDEX live_metrics_idx ON live_metrics (segment_type, segment_id);

CREATE VIEW metrics AS
    SELECT * FROM static_metrics
    UNION ALL
    SELECT * FROM live_metrics;

CREATE OR REPLACE RULE metrics_INS AS INSERT TO metrics
    DO INSTEAD
    INSERT INTO static_metrics VALUES (
        CASE WHEN NEW.id is null THEN nextval('static_metrics_id_seq') ELSE NEW.id END,
        NEW.segment_type,
        NEW.segment_id,
        NEW.metric_a,
        NEW.metric_b
    );

CREATE OR REPLACE RULE metrics_UPD AS ON UPDATE TO metrics
    DO INSTEAD
    UPDATE static_metrics
        SET
            id = NEW.id,
            segment_type = NEW.segment_type,  
            segment_id = NEW.segment_id,
            metric_a = NEW.metric_a,
            metric_b = NEW.metric_b
        WHERE id = OLD.id;

CREATE OR REPLACE RULE metrics_DEL AS ON DELETE TO metrics
    DO INSTEAD
    DELETE FROM static_metrics WHERE id = OLD.id;

Answer 1

Don't use a rule; it is likely to work different from how you believe it does, at least in certain cases. Use an INSTEAD OF trigger. Well, you have been warned.

Anyway, there is no way to have INSERT ... ON CONFLICT with a view, as a view cannot have constraints. Instead, just use simple INSERT and write the INSTEAD OF INSERT trigger to that it performs INSERT ... ON CONFLICT on the underlying table.