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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Sterpu Victor
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
Reply | Threaded
Open this post in threaded view
|

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
I also tested these commands on a PostgreSQL 12.1 and the error is the same.

------ Original Message ------
From: "Sterpu Victor" <[hidden email]>
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
Reply | Threaded
Open this post in threaded view
|

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

Sterpu Victor
Thank you...  :))

------ Original Message ------
From: "Pallavi Kumari" <[hidden email]>
To: "Sterpu Victor" <[hidden email]>
Sent: 2020-02-06 11:09:43 AM
Subject: Re: Error "is not a table or materialized view" when creating a unique index on a materialized view on PostgreSQL 9.5.10

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 <[hidden email]> wrote:
I also tested these commands on a PostgreSQL 12.1 and the error is the same.

------ Original Message ------
From: "Sterpu Victor" <[hidden email]>
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