How do I make this Postgres view that performs a join updatable?

I've got two tables, products and subscriptions:

CREATE TABLE products (
    id bigint NOT NULL,
    title character varying(75),
    description text,
    manufacturer_id bigint,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    mpn text,
    visible boolean DEFAULT false NOT NULL
);

CREATE TABLE subscriptions (
    id bigint NOT NULL,
    product_id bigint NOT NULL,
    user_id bigint NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);

In my application I usually need to know the number of subscribers for a product, and it is tricky sprinkling this logic in the application in all the correct places. So I would like to replace the products table with a view that already includes that information. So I did this:

ALTER TABLE ONLY products
  RENAME TO products_raw;

CREATE VIEW products AS
  SELECT products_raw.*, COALESCE(a.subscriptions_count, 0) AS subscriptions_count
  FROM products_raw
  LEFT OUTER JOIN (
    SELECT b.product_id, COUNT(*) subscriptions_count
    FROM   subscriptions b
    GROUP BY b.product_id
  ) a ON a.product_id = products_raw.id;

However the products view is not auto-updatable due to the JOIN - I would like any INSERT/UPDATE/DELETE actions to instead be performed on the products_raw table, ignoring the virtual subscriptions_count when applicable.

This is the first time I've created a view or rule, but I attempted this rule:

CREATE RULE products_insert_rule AS ON INSERT TO products DO INSTEAD
  INSERT INTO products_raw VALUES(NEW.*);

However Postgres doesn't like that:

PG::SyntaxError: ERROR:  INSERT has more expressions than target columns
LINE 13:         INSERT INTO products_raw VALUES(NEW.*);
                                                 ^

I also tried writing an INSTEAD OF INSERT trigger:

```
CREATE FUNCTION products_insert() RETURNS trigger AS $$
BEGIN
INSERT INTO products_raw VALUES (

You can't use NEW.*, because the view has columns that don't exist in the underlying table. You have to explicitly list out the columns you want to add. As people explained above, the reason this didn't work is that you did not provide a default. You might want to consider using COALESCE() for this purpose.

StackExchange Database Administrators Q#105957, answer score: 5