Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello

On 05/16/2016 08:49 AM, Shrikant Bhende wrote:
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

Neither there is an option to add a column.

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

What you are doing is actually a CREATE OR REPLACE VIEW and not a ALTER VIEW. In this case it is allowed to add columns *at the end of the list* (see below).

*
*
*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*

You need to drop the view before recreating it. Then it works. If you changed the access to the view with grants or revokes, you also neet to recreate them. They are dropped with the view.

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

http://www.postgresql.org/docs/current/static/sql-createview.html

CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. The calculations giving rise to the output columns may be completely different.

Regards,
Charles


Thanks.
--
Shrikant Bhende
+91-9975543712

--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux