You read views with SELECT. A simple view also accepts INSERT, UPDATE and DELETE. PostgreSQL passes each change to the table behind the view.
sql
CREATE VIEW cheap_products AS
SELECT id, name, price FROM products WHERE price < 10;
UPDATE cheap_products SET price = 9.00 WHERE id = 6;products
| id | name | price | stock |
|---|---|---|---|
| 6 | Cluster Socks | 9.00 | 120 |
| 7 | Linux Sticker Pack | 6.00 | 300 |
Which views are updatable
PostgreSQL can update a view when each view row comes from exactly one table row:
| Updatable | Not updatable |
|---|---|
one table in FROM | a join of tables |
| plain columns | GROUP BY, HAVING, COUNT(…) and other aggregates |
WHERE | DISTINCT, LIMIT, UNION |
On a view that is not updatable, the change fails:
text
ERROR: cannot update view "category_counts"
DETAIL: Views containing GROUP BY are not automatically updatable.Rows that the view hides
The INSERT below goes into products. The price is not below 10, so the view does not show the new row:
sql
INSERT INTO cheap_products (id, name, price) VALUES (13, 'Gold Mug', 80.00);INSERT … 80.00through the view
goes into
productsrow 13 is added
WHERE price < 10
cheap_productsrow 13 is hidden
Add WITH CHECK OPTION at the end of the view. Then PostgreSQL refuses every insert or update through the view that the view could not show:
sql
CREATE OR REPLACE VIEW cheap_products AS
SELECT id, name, price FROM products WHERE price < 10
WITH CHECK OPTION;text
ERROR: new row violates check option for view "cheap_products"
DETAIL: Failing row contains (14, Gold Mug, null, 80.00, 0).The failing row shows every column of products. The view does not list category_id and stock, so they get NULL and the default 0.