View not allowing to drop column (Bug or Feature enhancement )

From: Shrikant Bhende <shrikantbhende(dot)net(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: View not allowing to drop column (Bug or Feature enhancement )
Date: 2016-05-16 06:49:59
Message-ID: CADrerVWZrhXtEJd6wmg=hm_9hxVQEd2Gm4RMPEKza5OPjMAn6w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

While working on the view I came across an unusual behaviour of the view,
PostgreSQL do not allows to drop a column from the view, whereas same
pattern of Create and Replace view works while adding a column.

Please find below test for the same.

*Version info *
*===========*
postgres=# select version();

version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-55), 64-bit
(1 row)
\d+ orgdata
Table "public.orgdata"
Column | Type | Modifiers | Storage | Stats target |
Description
---------+-----------------------+-----------+----------+--------------+-------------
id | integer | not null | plain | |
name | character varying(20) | not null | extended | |
address | character varying(20) | | extended | |
age | integer | not null | plain | |
salary | numeric(10,0) | | main | |
Indexes:
"orgdata_pkey" PRIMARY KEY, btree (id)
Triggers:
example_trigger AFTER INSERT ON orgdata FOR EACH ROW EXECUTE PROCEDURE
auditlogfunc()

*Creating view *
postgres=# create or replace view vi1 as
select id , name from orgdata ;
CREATE VIEW

*Alter command do not have any option to drop column*
postgres=# alter view vi1
ALTER COLUMN OWNER TO RENAME TO SET SCHEMA

*To add columns it will work.*
*========================*
postgres=# create or replace view vi1 as
postgres-# select id, name, age from orgdata ;
CREATE VIEW

*While trying to drop a column by replacing view definition from view it
throws an error saying cannot drop column from view.*
*=====================================================================*
postgres=# create or replace view vi1 as select
id , name from orgdata ;
*ERROR: cannot drop columns from view*

If its not a bug and a limitation kindly guide me towards any documentation
where it is mentioned.

Thanks.
--
Shrikant Bhende
+91-9975543712

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Charles Clavadetscher 2016-05-16 07:12:06 Re: View not allowing to drop column (Bug or Feature enhancement )
Previous Message Haiming Zhang 2016-05-16 05:35:44 Re: Fast way to delete big table?