path: root/src/test/regress/expected/matview.out

-- create a table to use as a basis for views and materialized views in various combinations
CREATE TABLE mvtest_t (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL);
INSERT INTO mvtest_t VALUES
  (1, 'x', 2),
  (2, 'x', 3),
  (3, 'y', 5),
  (4, 'y', 7),
  (5, 'z', 11);
-- we want a view based on the table, too, since views present additional challenges
CREATE VIEW mvtest_tv AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type;
SELECT * FROM mvtest_tv ORDER BY type;
 type | totamt 
------+--------
 x    |      5
 y    |     12
 z    |     11
(3 rows)

-- create a materialized view with no data, and confirm correct behavior
EXPLAIN (costs off)
  CREATE MATERIALIZED VIEW mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA;
         QUERY PLAN         
----------------------------
 HashAggregate
   Group Key: type
   ->  Seq Scan on mvtest_t
(3 rows)

CREATE MATERIALIZED VIEW mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA;
SELECT relispopulated FROM pg_class WHERE oid = 'mvtest_tm'::regclass;
 relispopulated 
----------------
 f
(1 row)

SELECT * FROM mvtest_tm ORDER BY type;
ERROR:  materialized view "mvtest_tm" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.
REFRESH MATERIALIZED VIEW mvtest_tm;
SELECT relispopulated FROM pg_class WHERE oid = 'mvtest_tm'::regclass;
 relispopulated 
----------------
 t
(1 row)

CREATE UNIQUE INDEX mvtest_tm_type ON mvtest_tm (type);
SELECT * FROM mvtest_tm ORDER BY type;
 type | totamt 
------+--------
 x    |      5
 y    |     12
 z    |     11
(3 rows)

-- create various views
EXPLAIN (costs off)
  CREATE MATERIALIZED VIEW mvtest_tvm AS SELECT * FROM mvtest_tv ORDER BY type;
            QUERY PLAN            
----------------------------------
 Sort
   Sort Key: mvtest_t.type
   ->  HashAggregate
         Group Key: mvtest_t.type
         ->  Seq Scan on mvtest_t
(5 rows)

CREATE MATERIALIZED VIEW mvtest_tvm AS SELECT * FROM mvtest_tv ORDER BY type;
SELECT * FROM mvtest_tvm;
 type | totamt 
------+--------
 x    |      5
 y    |     12
 z    |     11
(3 rows)

CREATE MATERIALIZED VIEW mvtest_tmm AS SELECT sum(totamt) AS grandtot FROM mvtest_tm;
CREATE MATERIALIZED VIEW mvtest_tvmm AS SELECT sum(totamt) AS grandtot FROM mvtest_tvm;
CREATE UNIQUE INDEX mvtest_tvmm_expr ON mvtest_tvmm ((grandtot > 0));
CREATE UNIQUE INDEX mvtest_tvmm_pred ON mvtest_tvmm (grandtot) WHERE grandtot < 0;
CREATE VIEW mvtest_tvv AS SELECT sum(totamt) AS grandtot FROM mvtest_tv;
EXPLAIN (costs off)
  CREATE MATERIALIZED VIEW mvtest_tvvm AS SELECT * FROM mvtest_tvv;
            QUERY PLAN            
----------------------------------
 Aggregate
   ->  HashAggregate
         Group Key: mvtest_t.type
         ->  Seq Scan on mvtest_t
(4 rows)

CREATE MATERIALIZED VIEW mvtest_tvvm AS SELECT * FROM mvtest_tvv;
CREATE VIEW mvtest_tvvmv AS SELECT * FROM mvtest_tvvm;
CREATE MATERIALIZED VIEW mvtest_bb AS SELECT * FROM mvtest_tvvmv;
CREATE INDEX mvtest_aa ON mvtest_bb (grandtot);
-- check that plans seem reasonable
\d+ mvtest_tvm
                           Materialized view "public.mvtest_tvm"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 type   | text    |           |          |         | extended |              | 
 totamt | numeric |           |          |         | main     |              | 
View definition:
 SELECT mvtest_tv.type,
    mvtest_tv.totamt
   FROM mvtest_tv
  ORDER BY mvtest_tv.type;

\d+ mvtest_tvm
                           Materialized view "public.mvtest_tvm"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 type   | text    |           |          |         | extended |              | 
 totamt | numeric |           |          |         | main     |              | 
View definition:
 SELECT mvtest_tv.type,
    mvtest_tv.totamt
   FROM mvtest_tv
  ORDER BY mvtest_tv.type;

\d+ mvtest_tvvm
                           Materialized view "public.mvtest_tvvm"
  Column  |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
----------+---------+-----------+----------+---------+---------+--------------+-------------
 grandtot | numeric |           |          |         | main    |              | 
View definition:
 SELECT mvtest_tvv.grandtot
   FROM mvtest_tvv;

\d+ mvtest_bb
                            Materialized view "public.mvtest_bb"
  Column  |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
----------+---------+-----------+----------+---------+---------+--------------+-------------
 grandtot | numeric |           |          |         | main    |              | 
Indexes:
    "mvtest_aa" btree (grandtot)
View definition:
 SELECT mvtest_tvvmv.grandtot
   FROM mvtest_tvvmv;

-- test schema behavior
CREATE SCHEMA mvtest_mvschema;
ALTER MATERIALIZED VIEW mvtest_tvm SET SCHEMA mvtest_mvschema;
\d+ mvtest_tvm
\d+ mvtest_tvmm
                           Materialized view "public.mvtest_tvmm"
  Column  |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
----------+---------+-----------+----------+---------+---------+--------------+-------------
 grandtot | numeric |           |          |         | main    |              | 
Indexes:
    "mvtest_tvmm_expr" UNIQUE, btree ((grandtot > 0::numeric))
    "mvtest_tvmm_pred" UNIQUE, btree (grandtot) WHERE grandtot < 0::numeric
View definition:
 SELECT sum(mvtest_tvm.totamt) AS grandtot
   FROM mvtest_mvschema.mvtest_tvm;

SET search_path = mvtest_mvschema, public;
\d+ mvtest_tvm
                      Materialized view "mvtest_mvschema.mvtest_tvm"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 type   | text    |           |          |         | extended |              | 
 totamt | numeric |           |          |         | main     |              | 
View definition:
 SELECT mvtest_tv.type,
    mvtest_tv.totamt
   FROM mvtest_tv
  ORDER BY mvtest_tv.type;

-- modify the underlying table data
INSERT INTO mvtest_t VALUES (6, 'z', 13);
-- confirm pre- and post-refresh contents of fairly simple materialized views
SELECT * FROM mvtest_tm ORDER BY type;
 type | totamt 
------+--------
 x    |      5
 y    |     12
 z    |     11
(3 rows)

SELECT * FROM mvtest_tvm ORDER BY type;
 type | totamt 
------+--------
 x    |      5
 y    |     12
 z    |     11
(3 rows)

REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_tm;
REFRESH MATERIALIZED VIEW mvtest_tvm;
SELECT * FROM mvtest_tm ORDER BY type;
 type | totamt 
------+--------
 x    |      5
 y    |     12
 z    |     24
(3 rows)

SELECT * FROM mvtest_tvm ORDER BY type;
 type | totamt 
------+--------
 x    |      5
 y    |     12
 z    |     24
(3 rows)

RESET search_path;
-- confirm pre- and post-refresh contents of nested materialized views
EXPLAIN (costs off)
  SELECT * FROM mvtest_tmm;
       QUERY PLAN       
------------------------
 Seq Scan on mvtest_tmm
(1 row)

EXPLAIN (costs off)
  SELECT * FROM mvtest_tvmm;
       QUERY PLAN        
-------------------------
 Seq Scan on mvtest_tvmm
(1 row)

EXPLAIN (costs off)
  SELECT * FROM mvtest_tvvm;
       QUERY PLAN        
-------------------------
 Seq Scan on mvtest_tvvm
(1 row)

SELECT * FROM mvtest_tmm;
 grandtot 
----------
       28
(1 row)

SELECT * FROM mvtest_tvmm;
 grandtot 
----------
       28
(1 row)

SELECT * FROM mvtest_tvvm;
 grandtot 
----------
       28
(1 row)

REFRESH MATERIALIZED VIEW mvtest_tmm;
REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_tvmm;
ERROR:  cannot refresh materialized view "public.mvtest_tvmm" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.
REFRESH MATERIALIZED VIEW mvtest_tvmm;
REFRESH MATERIALIZED VIEW mvtest_tvvm;
EXPLAIN (costs off)
  SELECT * FROM mvtest_tmm;
       QUERY PLAN       
------------------------
 Seq Scan on mvtest_tmm
(1 row)

EXPLAIN (costs off)
  SELECT * FROM mvtest_tvmm;
       QUERY PLAN        
-------------------------
 Seq Scan on mvtest_tvmm
(1 row)

EXPLAIN (costs off)
  SELECT * FROM mvtest_tvvm;
       QUERY PLAN        
-------------------------
 Seq Scan on mvtest_tvvm
(1 row)

SELECT * FROM mvtest_tmm;
 grandtot 
----------
       41
(1 row)

SELECT * FROM mvtest_tvmm;
 grandtot 
----------
       41
(1 row)

SELECT * FROM mvtest_tvvm;
 grandtot 
----------
       41
(1 row)

-- test diemv when the mv does not exist
DROP MATERIALIZED VIEW IF EXISTS no_such_mv;
NOTICE:  materialized view "no_such_mv" does not exist, skipping
-- make sure invalid combination of options is prohibited
REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_tvmm WITH NO DATA;
ERROR:  CONCURRENTLY and WITH NO DATA options cannot be used together
-- no tuple locks on materialized views
SELECT * FROM mvtest_tvvm FOR SHARE;
ERROR:  cannot lock rows in materialized view "mvtest_tvvm"
-- test join of mv and view
SELECT type, m.totamt AS mtot, v.totamt AS vtot FROM mvtest_tm m LEFT JOIN mvtest_tv v USING (type) ORDER BY type;
 type | mtot | vtot 
------+------+------
 x    |    5 |    5
 y    |   12 |   12
 z    |   24 |   24
(3 rows)

-- make sure that dependencies are reported properly when they block the drop
DROP TABLE mvtest_t;
ERROR:  cannot drop table mvtest_t because other objects depend on it
DETAIL:  view mvtest_tv depends on table mvtest_t
materialized view mvtest_mvschema.mvtest_tvm depends on view mvtest_tv
materialized view mvtest_tvmm depends on materialized view mvtest_mvschema.mvtest_tvm
view mvtest_tvv depends on view mvtest_tv
materialized view mvtest_tvvm depends on view mvtest_tvv
view mvtest_tvvmv depends on materialized view mvtest_tvvm
materialized view mvtest_bb depends on view mvtest_tvvmv
materialized view mvtest_tm depends on table mvtest_t
materialized view mvtest_tmm depends on materialized view mvtest_tm
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
-- make sure dependencies are dropped and reported
-- and make sure that transactional behavior is correct on rollback
-- incidentally leaving some interesting materialized views for pg_dump testing
BEGIN;
DROP TABLE mvtest_t CASCADE;
NOTICE:  drop cascades to 9 other objects
DETAIL:  drop cascades to view mvtest_tv
drop cascades to materialized view mvtest_mvschema.mvtest_tvm
drop cascades to materialized view mvtest_tvmm
drop cascades to view mvtest_tvv
drop cascades to materialized view mvtest_tvvm
drop cascades to view mvtest_tvvmv
drop cascades to materialized view mvtest_bb
drop cascades to materialized view mvtest_tm
drop cascades to materialized view mvtest_tmm
ROLLBACK;
-- some additional tests not using base tables
CREATE VIEW mvtest_vt1 AS SELECT 1 moo;
CREATE VIEW mvtest_vt2 AS SELECT moo, 2*moo FROM mvtest_vt1 UNION ALL SELECT moo, 3*moo FROM mvtest_vt1;
\d+ mvtest_vt2
                          View "public.mvtest_vt2"
  Column  |  Type   | Collation | Nullable | Default | Storage | Description 
----------+---------+-----------+----------+---------+---------+-------------
 moo      | integer |           |          |         | plain   | 
 ?column? | integer |           |          |         | plain   | 
View definition:
 SELECT mvtest_vt1.moo,
    2 * mvtest_vt1.moo AS "?column?"
   FROM mvtest_vt1
UNION ALL
 SELECT mvtest_vt1.moo,
    3 * mvtest_vt1.moo
   FROM mvtest_vt1;

CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM mvtest_vt2 UNION ALL SELECT moo, 3*moo FROM mvtest_vt2;
\d+ mv_test2
                            Materialized view "public.mv_test2"
  Column  |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
----------+---------+-----------+----------+---------+---------+--------------+-------------
 moo      | integer |           |          |         | plain   |              | 
 ?column? | integer |           |          |         | plain   |              | 
View definition:
 SELECT mvtest_vt2.moo,
    2 * mvtest_vt2.moo AS "?column?"
   FROM mvtest_vt2
UNION ALL
 SELECT mvtest_vt2.moo,
    3 * mvtest_vt2.moo
   FROM mvtest_vt2;

CREATE MATERIALIZED VIEW mv_test3 AS SELECT * FROM mv_test2 WHERE moo = 12345;
SELECT relispopulated FROM pg_class WHERE oid = 'mv_test3'::regclass;
 relispopulated 
----------------
 t
(1 row)

DROP VIEW mvtest_vt1 CASCADE;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to view mvtest_vt2
drop cascades to materialized view mv_test2
drop cascades to materialized view mv_test3
-- test that duplicate values on unique index prevent refresh
CREATE TABLE mvtest_foo(a, b) AS VALUES(1, 10);
CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo;
CREATE UNIQUE INDEX ON mvtest_mv(a);
INSERT INTO mvtest_foo SELECT * FROM mvtest_foo;
REFRESH MATERIALIZED VIEW mvtest_mv;
ERROR:  could not create unique index "mvtest_mv_a_idx"
DETAIL:  Key (a)=(1) is duplicated.
REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv;
ERROR:  new data for materialized view "mvtest_mv" contains duplicate rows without any null columns
DETAIL:  Row: (1,10)
DROP TABLE mvtest_foo CASCADE;
NOTICE:  drop cascades to materialized view mvtest_mv
-- make sure that all columns covered by unique indexes works
CREATE TABLE mvtest_foo(a, b, c) AS VALUES(1, 2, 3);
CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo;
CREATE UNIQUE INDEX ON mvtest_mv (a);
CREATE UNIQUE INDEX ON mvtest_mv (b);
CREATE UNIQUE INDEX on mvtest_mv (c);
INSERT INTO mvtest_foo VALUES(2, 3, 4);
INSERT INTO mvtest_foo VALUES(3, 4, 5);
REFRESH MATERIALIZED VIEW mvtest_mv;
REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv;
DROP TABLE mvtest_foo CASCADE;
NOTICE:  drop cascades to materialized view mvtest_mv
-- allow subquery to reference unpopulated matview if WITH NO DATA is specified
CREATE MATERIALIZED VIEW mvtest_mv1 AS SELECT 1 AS col1 WITH NO DATA;
CREATE MATERIALIZED VIEW mvtest_mv2 AS SELECT * FROM mvtest_mv1
  WHERE col1 = (SELECT LEAST(col1) FROM mvtest_mv1) WITH NO DATA;
DROP MATERIALIZED VIEW mvtest_mv1 CASCADE;
NOTICE:  drop cascades to materialized view mvtest_mv2
-- make sure that types with unusual equality tests work
CREATE TABLE mvtest_boxes (id serial primary key, b box);
INSERT INTO mvtest_boxes (b) VALUES
  ('(32,32),(31,31)'),
  ('(2.0000004,2.0000004),(1,1)'),
  ('(1.9999996,1.9999996),(1,1)');
CREATE MATERIALIZED VIEW mvtest_boxmv AS SELECT * FROM mvtest_boxes;
CREATE UNIQUE INDEX mvtest_boxmv_id ON mvtest_boxmv (id);
UPDATE mvtest_boxes SET b = '(2,2),(1,1)' WHERE id = 2;
REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_boxmv;
SELECT * FROM mvtest_boxmv ORDER BY id;
 id |              b              
----+-----------------------------
  1 | (32,32),(31,31)
  2 | (2,2),(1,1)
  3 | (1.9999996,1.9999996),(1,1)
(3 rows)

DROP TABLE mvtest_boxes CASCADE;
NOTICE:  drop cascades to materialized view mvtest_boxmv
-- make sure that column names are handled correctly
CREATE TABLE mvtest_v (i int, j int);
CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj, kk) AS SELECT i, j FROM mvtest_v; -- error
ERROR:  too many column names were specified
CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj) AS SELECT i, j FROM mvtest_v; -- ok
CREATE MATERIALIZED VIEW mvtest_mv_v_2 (ii) AS SELECT i, j FROM mvtest_v; -- ok
CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj, kk) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- error
ERROR:  too many column names were specified
CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- ok
CREATE MATERIALIZED VIEW mvtest_mv_v_4 (ii) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- ok
ALTER TABLE mvtest_v RENAME COLUMN i TO x;
INSERT INTO mvtest_v values (1, 2);
CREATE UNIQUE INDEX mvtest_mv_v_ii ON mvtest_mv_v (ii);
REFRESH MATERIALIZED VIEW mvtest_mv_v;
UPDATE mvtest_v SET j = 3 WHERE x = 1;
REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv_v;
REFRESH MATERIALIZED VIEW mvtest_mv_v_2;
REFRESH MATERIALIZED VIEW mvtest_mv_v_3;
REFRESH MATERIALIZED VIEW mvtest_mv_v_4;
SELECT * FROM mvtest_v;
 x | j 
---+---
 1 | 3
(1 row)

SELECT * FROM mvtest_mv_v;
 ii | jj 
----+----
  1 |  3
(1 row)

SELECT * FROM mvtest_mv_v_2;
 ii | j 
----+---
  1 | 3
(1 row)

SELECT * FROM mvtest_mv_v_3;
 ii | jj 
----+----
  1 |  3
(1 row)

SELECT * FROM mvtest_mv_v_4;
 ii | j 
----+---
  1 | 3
(1 row)

DROP TABLE mvtest_v CASCADE;
NOTICE:  drop cascades to 4 other objects
DETAIL:  drop cascades to materialized view mvtest_mv_v
drop cascades to materialized view mvtest_mv_v_2
drop cascades to materialized view mvtest_mv_v_3
drop cascades to materialized view mvtest_mv_v_4
-- Check that unknown literals are converted to "text" in CREATE MATVIEW,
-- so that we don't end up with unknown-type columns.
CREATE MATERIALIZED VIEW mv_unspecified_types AS
  SELECT 42 as i, 42.5 as num, 'foo' as u, 'foo'::unknown as u2, null as n;
\d+ mv_unspecified_types
                      Materialized view "public.mv_unspecified_types"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 i      | integer |           |          |         | plain    |              | 
 num    | numeric |           |          |         | main     |              | 
 u      | text    |           |          |         | extended |              | 
 u2     | text    |           |          |         | extended |              | 
 n      | text    |           |          |         | extended |              | 
View definition:
 SELECT 42 AS i,
    42.5 AS num,
    'foo'::text AS u,
    'foo'::text AS u2,
    NULL::text AS n;

SELECT * FROM mv_unspecified_types;
 i  | num  |  u  | u2  | n 
----+------+-----+-----+---
 42 | 42.5 | foo | foo | 
(1 row)

DROP MATERIALIZED VIEW mv_unspecified_types;
-- make sure that create WITH NO DATA does not plan the query (bug #13907)
create materialized view mvtest_error as select 1/0 as x;  -- fail
ERROR:  division by zero
create materialized view mvtest_error as select 1/0 as x with no data;
refresh materialized view mvtest_error;  -- fail here
ERROR:  division by zero
drop materialized view mvtest_error;
-- make sure that matview rows can be referenced as source rows (bug #9398)
CREATE TABLE mvtest_v AS SELECT generate_series(1,10) AS a;
CREATE MATERIALIZED VIEW mvtest_mv_v AS SELECT a FROM mvtest_v WHERE a <= 5;
DELETE FROM mvtest_v WHERE EXISTS ( SELECT * FROM mvtest_mv_v WHERE mvtest_mv_v.a = mvtest_v.a );
SELECT * FROM mvtest_v;
 a  
----
  6
  7
  8
  9
 10
(5 rows)

SELECT * FROM mvtest_mv_v;
 a 
---
 1
 2
 3
 4
 5
(5 rows)

DROP TABLE mvtest_v CASCADE;
NOTICE:  drop cascades to materialized view mvtest_mv_v
-- make sure running as superuser works when MV owned by another role (bug #11208)
CREATE ROLE regress_user_mvtest;
SET ROLE regress_user_mvtest;
-- this test case also checks for ambiguity in the queries issued by
-- refresh_by_match_merge(), by choosing column names that intentionally
-- duplicate all the aliases used in those queries
CREATE TABLE mvtest_foo_data AS SELECT i,
  i+1 AS tid,
  md5(random()::text) AS mv,
  md5(random()::text) AS newdata,
  md5(random()::text) AS newdata2,
  md5(random()::text) AS diff
  FROM generate_series(1, 10) i;
CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;
CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;
ERROR:  relation "mvtest_mv_foo" already exists
CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;
NOTICE:  relation "mvtest_mv_foo" already exists, skipping
CREATE UNIQUE INDEX ON mvtest_mv_foo (i);
RESET ROLE;
REFRESH MATERIALIZED VIEW mvtest_mv_foo;
REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv_foo;
DROP OWNED BY regress_user_mvtest CASCADE;
DROP ROLE regress_user_mvtest;
-- make sure that create WITH NO DATA works via SPI
BEGIN;
CREATE FUNCTION mvtest_func()
  RETURNS void AS $$
BEGIN
  CREATE MATERIALIZED VIEW mvtest1 AS SELECT 1 AS x;
  CREATE MATERIALIZED VIEW mvtest2 AS SELECT 1 AS x WITH NO DATA;
END;
$$ LANGUAGE plpgsql;
SELECT mvtest_func();
 mvtest_func 
-------------
 
(1 row)

SELECT * FROM mvtest1;
 x 
---
 1
(1 row)

SELECT * FROM mvtest2;
ERROR:  materialized view "mvtest2" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.
ROLLBACK;
-- INSERT privileges if relation owner is not allowed to insert.
CREATE SCHEMA matview_schema;
CREATE USER regress_matview_user;
ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
  REVOKE INSERT ON TABLES FROM regress_matview_user;
GRANT ALL ON SCHEMA matview_schema TO public;
SET SESSION AUTHORIZATION regress_matview_user;
CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS
  SELECT generate_series(1, 10) WITH DATA;
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
  CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS
  SELECT generate_series(1, 10) WITH DATA;
              QUERY PLAN              
--------------------------------------
 ProjectSet (actual rows=10 loops=1)
   ->  Result (actual rows=1 loops=1)
(2 rows)

REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2;
CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS
  SELECT generate_series(1, 10) WITH NO DATA;
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
  CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS
  SELECT generate_series(1, 10) WITH NO DATA;
          QUERY PLAN           
-------------------------------
 ProjectSet (never executed)
   ->  Result (never executed)
(2 rows)

REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2;
RESET SESSION AUTHORIZATION;
ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
  GRANT INSERT ON TABLES TO regress_matview_user;
DROP SCHEMA matview_schema CASCADE;
NOTICE:  drop cascades to 4 other objects
DETAIL:  drop cascades to materialized view matview_schema.mv_withdata1
drop cascades to materialized view matview_schema.mv_withdata2
drop cascades to materialized view matview_schema.mv_nodata1
drop cascades to materialized view matview_schema.mv_nodata2
DROP USER regress_matview_user;
-- CREATE MATERIALIZED VIEW ... IF NOT EXISTS
CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1;
CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error
ERROR:  relation "matview_ine_tab" already exists
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
  SELECT 1 / 0; -- ok
NOTICE:  relation "matview_ine_tab" already exists, skipping
CREATE MATERIALIZED VIEW matview_ine_tab AS
  SELECT 1 / 0 WITH NO DATA; -- error
ERROR:  relation "matview_ine_tab" already exists
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
  SELECT 1 / 0 WITH NO DATA; -- ok
NOTICE:  relation "matview_ine_tab" already exists, skipping
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
  CREATE MATERIALIZED VIEW matview_ine_tab AS
    SELECT 1 / 0; -- error
ERROR:  relation "matview_ine_tab" already exists
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
  CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
    SELECT 1 / 0; -- ok
NOTICE:  relation "matview_ine_tab" already exists, skipping
 QUERY PLAN 
------------
(0 rows)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
  CREATE MATERIALIZED VIEW matview_ine_tab AS
    SELECT 1 / 0 WITH NO DATA; -- error
ERROR:  relation "matview_ine_tab" already exists
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
  CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
    SELECT 1 / 0 WITH NO DATA; -- ok
NOTICE:  relation "matview_ine_tab" already exists, skipping
 QUERY PLAN 
------------
(0 rows)

DROP MATERIALIZED VIEW matview_ine_tab;