Postgresql ERROR: cannot drop columns from view

Question

I'm new to postgresql. Can anyone suggest the reason and solution to this error? However it works if I select an extra sum(s.length) but i don't won't this in my results.

code:

create or replace view q1("group",album,year) as
    select g.name, a.title, a.year
    from Groups g, Albums a, Songs s
    where a.made_by = g.id and s.on_album = a.id
    group by a.title, g.name, a.year
    order by sum(s.length) desc 
    limit 1;

Error message:

ERROR: cannot drop columns from view

Answer 1

As It's been mentioned in PostgreSQL documentations:

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.

Reference Postgres CREATE VIEW docs

simply put, you can add a new column to the end of existing columns but you can't drop , reorder , rename or change datatype of existing columns.

So in your case, you have to drop the whole view and recreate it.