Category Archives: Databases

Database Views in Postgres

postgres

A few weeks ago I gave a presentation at the New York City PostgreSQL User Group about Database Views in Postgres. Most technology professionals that work with databases are familiar with database views but (at least in Postgres) there are some really interesting advanced features that are possible. In Postgres you can set up rules so that you can not only query views with the select statement but you can also perform updates, inserts and deletes which will be reflected in changes to the underlying view’s tables. For example, here is how to setup and execute a delete statement on a database view named customer_view:

CREATE RULE delete_customer_view AS ON DELETE TO customer_view DO INSTEAD (
DELETE FROM customer WHERE id=OLD.id
);

DELETE FROM customer_view WHERE id = 1;

Excessive database views and complicated rules can easily take down any system so it’s best to use these features sparingly. It’s nice to have the ability though for sure.

The full presentation is short and easy to understand so if you are a Postgres user go take a look the presentation slides here.