Insert into postgres view using defaults based on view definition

Question

Нello! Say that a vehicle can be of type "car", "truck", or "motorcycle". Each vehicle has a top_speed (in km/h) and a license_plate string.

E.g.

CREATE TABLE vehicle (
  type VARCHAR(20) NOT NULL,
  top_speed INTEGER NOT NULL,
  license_plate VARCHAR(10) NOT NULL
);

INSERT INTO vehicle (type, top_speed, license_plate)
  VALUES
    ('car', 120, 'abc123'),
    ('truck', 110, 'def456'),
    ('motorcycle', 140, 'ghi789');

Now add views for each type of vehicle:

CREATE VIEW car AS (SELECT * FROM vehicle WHERE type='car');
CREATE VIEW truck AS (SELECT * FROM vehicle WHERE type='truck');
CREATE VIEW motorcycle AS (SELECT * FROM vehicle WHERE type='motorcycle');

All this is fine and dandy. But I run into an uncomfortable situation when I try to insert into these views:

INSERT INTO car (type, top_speed, license_plate)
  VALUES
    ('car', 160, 'v4n1ty');

My issue is that I'm already inserting into a view called "car"... why should I have to bother to specify that type = 'car'?

If I omit the type column from this insert query I'll get an error that the type column isn't allowed to contain NULL. It seems like postgres won't default omitted values even when they could be gleaned from the view's definition.

Is there a way I can get postgres to look to the view's definition in order to provide defaults for omitted columns in INSERT queries?

Answer 1

There is very powerful rule system in PostgreSQL.

Additionally to your code:

create rule car_insert as on insert to car do instead
  insert into vehicle(type, top_speed, license_plate)
    values('car', new.top_speed, new.license_plate);

insert into car(top_speed, license_plate) values(160,'v4n1ty');

table car;
┌──────┬───────────┬───────────────┐
│ type │ top_speed │ license_plate │
├──────┼───────────┼───────────────┤
│ car  │       120 │ abc123        │
│ car  │       160 │ v4n1ty        │
└──────┴───────────┴───────────────┘