PgSQL物化视图 增量更新

Question

问题遇到的现象和发生背景

PgSQL 版本：PostgreSQL 10.19
要求：pgsqL 做物化视图，并且规定每天凌晨1点做自动增量更新

SQL是类似这样的 ：
CREATE MATERIALIZED VIEW myview
REFRESH
START WITH DATE_FORMAT(now() + interval 1 day, '%Y-%m-%d 02:00:00')
NEXT DATE_FORMAT(now() + interval 1 day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt
FROM goods G
LEFT JOIN catalog C ON G.catalog_id = C.id
WHERE
....
GROUP BY C.id

报错：

请问下pgSQL 能实现自动定时增量刷新么

Answer 1

该回答引用ChatGPT
请参考下面的解决方案，如果有帮助，还请点击 “采纳” 感谢！
您的语法有错误。在 PostgreSQL 10.19 中，在 REFRESH 语句之前必须添加 WITH 关键字。

CREATE MATERIALIZED VIEW myview
WITH (
  REFRESH
  START WITH DATE_FORMAT(now() + interval '1 day', '%Y-%m-%d 02:00:00')
  NEXT DATE_FORMAT(now() + interval '1 day', '%Y-%m-%d 02:00:00')
) AS
SELECT count(*) as cnt
FROM goods G
LEFT JOIN catalog C ON G.catalog_id = C.id
WHERE
....
GROUP BY C.id