Thread: Error "is not a table or materialized view" when creating a unique index on amaterialized 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

hi,

in creating unique index you are getting error because u mentioned wrong view name(focg_uni_check).

it should be focg_uni not focg_uni_check.

current syntax:

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

On Thu, Feb 6, 2020 at 2:35 PM Sterpu Victor <victor@caido.ro> wrote:

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

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

From: "Sterpu Victor" <victor@caido.ro>

To: pgsql-admin@postgresql.org

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