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
