CODEWITHSUNDEEP
postgresql
A_Lady
A_Lady

Reputation: 1

Postgresql - create materialized view

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?

Upvotes: 0

Views: 972

Answers (1)

pifor
pifor

Reputation: 7882

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;

Upvotes: 1

Related Questions