Postgresql - create materialized view

Question

CREATE MATERIALIZED VIEW Available
REFRESH FORCE
START WITH CURRENT_DATE NEXT CURRENT_DATE + 1
AS
SELECT Table.item_id AS item_id, 
Table.reserv_id AS reserv_id  
FROM Table
LEFT OUTER JOIN Reserv ON Reserv.reserv_id = Table.reserv_id  
WHERE Reserv.start_date > CURRENT_DATE
OR Reserv.end_date < CURRENT_DATE 
OR Table.reserv_id IS NULL;

I want to create view which is update once a day but I get error: " syntax error at or near "REFRESH" ". What is wrong with it?

You syntax doesn't seem to be documented. Where did you get the idea from? — sticky bit
– sticky bit, Commented Jun 16, 2020 at 10:55

pifor · Accepted Answer · 2020-06-16 11:39:41Z

1

You cannot use any REFRESH clause in PostgreSQL because it does not exist in PostgreSQL.

If the following query is correct in your database:

SELECT Table.item_id AS item_id, 
Table.reserv_id AS reserv_id  
FROM Table
LEFT OUTER JOIN Reserv ON Reserv.reserv_id = Table.reserv_id  
WHERE Reserv.start_date > CURRENT_DATE
OR Reserv.end_date < CURRENT_DATE 
OR Table.reserv_id IS NULL;

Following statement should work:

    CREATE MATERIALIZED VIEW Available
    AS
    SELECT Table.item_id AS item_id, 
    Table.reserv_id AS reserv_id  
    FROM Table
    LEFT OUTER JOIN Reserv ON Reserv.reserv_id = Table.reserv_id  
    WHERE Reserv.start_date > CURRENT_DATE
    OR Reserv.end_date < CURRENT_DATE 
    OR Table.reserv_id IS NULL;

edited Jun 16, 2020 at 11:39

answered Jun 16, 2020 at 11:04

pifor

7,9722 gold badges11 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Slava Rozhnev Over a year ago

Don't forget to refresh materialized views by event using trigger or by interval using crontab

Collectives™ on Stack Overflow

Postgresql - create materialized view

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related