- I'm trying to create a non-updatable view in PostgreSQL but every view I've created is continue updating after inserting some data in tables. What am I doing wrong?
- What is the difference between materialized view and an updatable view?
- How can I quickly check if the view is updatable or not?
Here are I tried to create three types of views:
-- Updatable view
CREATE VIEW vip_tickets_for_events AS
SELECT events.id, events.name, COUNT(tickets.id) as vip_tickets_num FROM events
JOIN tickets on events.id = tickets.event_id
WHERE tickets.type = 'VIP'
GROUP BY events.id, events.name;
-- Non-updatable view
CREATE VIEW tickets_for_events AS
SELECT vip_tickets.name, vip_tickets.vip_tickets_num, general_tickets.general_tickets_num
FROM (SELECT events.name, COUNT(tickets.id) as vip_tickets_num FROM events
JOIN tickets on events.id = tickets.event_id
WHERE tickets.type = 'VIP'
GROUP BY events.name) AS vip_tickets
JOIN
(SELECT events.name, COUNT(tickets.id) as general_tickets_num FROM events
JOIN tickets on events.id = tickets.event_id
WHERE tickets.type = 'General'
GROUP BY events.name) AS general_tickets
ON vip_tickets.name = general_tickets.name;
-- Materialized view
CREATE MATERIALIZED VIEW average_ticket_prices_for_events AS
SELECT events.id, events.name, AVG(tickets.price) as average_price FROM events
JOIN tickets on events.id = tickets.event_id
GROUP BY events.id, events.name;
CodePudding user response:
I'm trying to create a non-updatable view in PostgreSQL but every view I've created is continue updating after inserting some data in tables.
You're mixing up terms here. An "updatable view" is a View that allows data to be inserted/updated/deleted:
insert into some_view(col1, col2) values('val_1', 100);
Here is an example of such a view you can play with
The docs says:
Simple views are automatically updatable: the system will allow INSERT, UPDATE and DELETE statements to be used on the view in the same way as on a regular table.
See that doc to get when view is simple enough.
Since all your views contain more than 1 table in FROM area, they are non-updatable. In other words, you won't be able to run insert/update/delete against them directly.
When you're saying "every view I've created is continue updating after inserting some data in tables" it means to me you're expecting view to keep showing old data after source table has changed.
That won't work with a regular view and it leads us to your next question
- What is the difference between materialized view and an updatable view?
Again, I ignore the word "updatable" as it makes no sence.
A view is just a stored query. Whenever you say
select * from some_view
the underlying query will be executed. You always get the actual data.
A Materialized view is a shapshot of a table. It keeps the query AND results of the last execution (called "refresh").
So, if you created or refreshed a materialized view days ago, it'll show you data from days ago despite of table data has changed or not.
And I believe "materialized view" is what you are trying to achieve saying "non-updatable view"
- How can I quickly check if the view is updatable or not?
- if you need to check whether you can run insert/update/delete against a view you may just try it or check if it's source code is simple enough
- if you need to check whether an object is a view or a materialized view, this queries will help you
select *
from pg_matviews
where matviewname = 'test_mview'; -- whether an object is a materialized view (and won't get updated automatically after table data changed)
select *
from pg_views
where viewname = 'test_view'; -- whether it is a view (ad will get updated after changes in a source table)