PostgreSQL | Using different types of views

Question

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;

Could you please show us how your attempts of creating views? So we would be able to tell you what was wrong? — ekochergin, May 03 '22 at 08:52
Please create an example that shows this behaviour: https://dbfiddle.uk/?rdbms=postgres_14 — Frank Heikens, May 03 '22 at 09:05
By the way, REVOKE is your best friend to revoke UPDATEs to a table. — Frank Heikens, May 03 '22 at 09:06
Check the manual, it answers most (if not all) of your questions: https://www.postgresql.org/docs/current/sql-createview.html — Frank Heikens, May 03 '22 at 10:23

score 3 · Accepted Answer · edited May 03 '22 at 11:10

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)

PostgreSQL | Using different types of views

1 Answers1