PostgreSQL do not allows to drop a column from the view, whereas same pattern of Create and Replace view works while adding a column.
=========== 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
