Hi,
*
*
*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.
Sorry to say but If we need to drop and replace then what is use of "Create OR Replace " syntax?
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.
If this is the limitation. Is community is planning update this or add this feature soon?
On Mon, May 16, 2016 at 12:50 PM, Francisco Olarte <folarte@xxxxxxxxxxxxxx> wrote:
On Mon, May 16, 2016 at 8:49 AM, Shrikant Bhende
<shrikantbhende.net@xxxxxxxxx> wrote:
> 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.
This is probably because you are using create or replace, which is
normally used to switch things in place and so it needs them to be
compatible with the old ones. A view with an extra column can be used
instead of the old one, but a view with less columns can not. I do not
see the 'not dropping' part as unusual, and the 'can add columns',
well, I see them as a little unusual on a create or replace but I see
the point in hallowing it, so just a little.
> Alter command do not have any option to drop column
> postgres=# alter view vi1
> ALTER COLUMN OWNER TO RENAME TO SET SCHEMA
Well, it is a view, not a table. They are basically shorthands for
queries and places to attach triggers, so its normal they do not have
as much management options.
> If its not a bug and a limitation kindly guide me towards any documentation
> where it is mentioned.
Right at the top of create view? :
>>>>
Description
CREATE VIEW defines a view of a query. The view is not physically
materialized. Instead, the query is run every time the view is
referenced in a query.
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.
<<<<<<
Francisco Olarte.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Sachin Kotwal
