Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1izd6Q-0007cC-AW for pgsql-admin@arkaria.postgresql.org; Thu, 06 Feb 2020 09:05:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1izd6P-0002GX-1y for pgsql-admin@arkaria.postgresql.org; Thu, 06 Feb 2020 09:05:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1izd6O-0002Fa-Oe for pgsql-admin@lists.postgresql.org; Thu, 06 Feb 2020 09:05:36 +0000 Received: from mail.caido.ro ([86.107.248.7]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1izd6I-0003c2-Gn for pgsql-admin@postgresql.org; Thu, 06 Feb 2020 09:05:36 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.caido.ro (Postfix) with ESMTP id 633D91E9AE9 for ; Thu, 6 Feb 2020 11:06:28 +0200 (EET) Received: from mail.caido.ro ([127.0.0.1]) by localhost (caido.ro [127.0.0.1]) (amavisd-new, port 10032) with ESMTP id UeaMnsGhrfTj; Thu, 6 Feb 2020 11:06:27 +0200 (EET) Received: from localhost (localhost [127.0.0.1]) by mail.caido.ro (Postfix) with ESMTP id 8874A1E9AE1; Thu, 6 Feb 2020 11:06:27 +0200 (EET) DKIM-Filter: OpenDKIM Filter v2.9.2 mail.caido.ro 8874A1E9AE1 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=caido.ro; s=2B8A3026-9717-11E9-9D82-7DF1CDD8AB23; t=1580979987; bh=tFw7WJEf/pVeasckzhJRmkS3apExTDRNEhlbwVyrjE4=; h=From:To:Subject:Date:Message-Id:Reply-To:Mime-Version: Content-Type; b=amlL3THAICSNKyFCe6GGpqytN6BUCek6csYvxDjuq1irGJU1CoLbgPaRkdb/atAoz nD4RTUgQTdKdy56mEDd+PYZ39mrRS3ePDonQLv+e+GtogEZgYpPUI0ZuzIW3/yJEzW Quqx+y7jMUbUMvC6gptx7MWzTmfbMVYhpjox2jbE= X-Virus-Scanned: amavisd-new at caido.ro Received: from mail.caido.ro ([127.0.0.1]) by localhost (caido.ro [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id pbaUqo7xFke6; Thu, 6 Feb 2020 11:06:27 +0200 (EET) Received: from [192.168.88.2] (unknown [89.43.152.14]) by mail.caido.ro (Postfix) with ESMTPSA id 4EB8B1E9A7F; Thu, 6 Feb 2020 11:06:27 +0200 (EET) From: "Sterpu Victor" To: "Sterpu Victor" , pgsql-admin@postgresql.org Subject: Re: Error "is not a table or materialized view" when creating a unique index on a materialized view on PostgreSQL 9.5.10 Date: Thu, 06 Feb 2020 09:05:21 +0000 Message-Id: In-Reply-To: References: Reply-To: "Sterpu Victor" User-Agent: eM_Client/7.0.27943.0 Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="------=_MBDCE69B48-2FA1-42D2-9732-C49D270F00F6" X-Antivirus: Avast (VPS 200205-0, 02/05/2020), Outbound message X-Antivirus-Status: Clean List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --------=_MBDCE69B48-2FA1-42D2-9732-C49D270F00F6 Content-Type: text/plain; format=flowed; charset=utf-8 Content-Transfer-Encoding: quoted-printable I also tested these commands on a PostgreSQL 12.1 and the error is the=20 same. ------ Original Message ------ From: "Sterpu Victor" 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=20 unique index on a materialized view on PostgreSQL 9.5.10 >Hello > >I have PostgreSQL 9.5.10 and when I receive the error: is not a= =20 >table or materialized view when I try to create a unique index on a=20 >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 =3D 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 --------=_MBDCE69B48-2FA1-42D2-9732-C49D270F00F6 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
I also tested these commands on a PostgreSQL 12.1 and the error= is the same.

------ Original Message ------
From: "Sterpu Victor" <victor@ca= ido.ro>
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>=C2=A0is= not a table or materialized view when I try to create a unique index on= a=C2=A0materialized view.

These are the SQL commands:
CRE= ATE MATERIALIZED VIEW public.focg_uni AS
SELECT f.nrfo,
=C2= =A0 =C2=A0to_char(min(fd.validfrom), 'YYYY'::text) AS anul_internarii =C2=A0 FROM focg f
=C2=A0 =C2=A0 JOIN focgdepartment fd ON fd.idf= ocg =3D f.id
=C2=A0GROUP 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: =C2= =A0"focg_uni_check" is not a table or materialized view
********** = Error **********

ERROR: "focg_uni_check" is not a table or mater= ialized view
SQL state: 42809


Thank you
--------=_MBDCE69B48-2FA1-42D2-9732-C49D270F00F6--