Re: Error "is not a table or materialized view" when creating a unique index on a materialized view on PostgreSQL 9.5.10

["Sterpu Victor" <victor@xxxxxxxx>]

I also tested these commands on a PostgreSQL 12.1 and the error is the same.

------ Original Message ------

From: "Sterpu Victor" <victor@xxxxxxxx>

To: pgsql-admin@xxxxxxxxxxxxxx

Sent: 2020-02-06 11:03:28 AM

Subject: Error "is not a table or materialized view" when creating a unique index on a materialized view on PostgreSQL 9.5.10

Hello

I have PostgreSQL 9.5.10 and when I receive the error: <table> is not a table or materialized view when I try to create a unique index on a materialized view.

These are the SQL commands:

CREATE MATERIALIZED VIEW public.focg_uni AS
SELECT f.nrfo,
   to_char(min(fd.validfrom), 'YYYY'::text) AS anul_internarii
  FROM focg f
    JOIN focgdepartment fd ON fd.idfocg = f.id
 GROUP BY f.nrfo
WITH DATA;

REFRESH MATERIALIZED VIEW public.focg_uni

CREATE UNIQUE INDEX focg_uni_check_index ON focg_uni_check(nrfo, an);

ERROR:  "focg_uni_check" is not a table or materialized view
********** Error **********

ERROR: "focg_uni_check" is not a table or materialized view
SQL state: 42809

Thank you