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 01:00:00')
NEXT DATE_FORMAT(now() + interval 1 day, '%Y-%m-%d 01: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

报错：

据说在 PostgreSQL 10.19 中，在 REFRESH 语句之前必须添加 WITH 关键字
SQL改为：

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

报错：

请大家帮忙看看 到底问题出在哪里？？ 谢谢了

Answer 1

这个问题很简单呀