Postgres alter view add column

Question

I want to alter a view and add a new column in it. I have:

ALTER VIEW folders_contents AS SELECT files.id, files.name, files.filesize, files.updated, files.deleted, FROM files UNION ALL SELECT folders.id, folders.name, 0 AS filesize, folders.updated, folders.deleted, FROM folders ORDER BY 8, 2 GO

The problem is that it shows:

[Err] ERROR: syntax error at or near "AS"

Is the first time I have to do with views, I need some help :)

There is no GO in Postgres. And ALTER VIEW does not what you think it does which is clearly documented in the manual — user330315
– user330315, Commented Mar 14, 2015 at 9:36

Houari · Accepted Answer · 2015-03-14 08:44:22Z

ALTER VIEW changes various auxiliary properties of a view. (If you want to modify the view's defining query, use CREATE OR REPLACE VIEW.)

Use CREATE OR REPLACE INSTEAD

In your case, it will be something like:

CREATE OR REPLACE VIEW folders_contents AS SELECT files.id, files.name, files.filesize, files.updated, files.deleted, FROM files UNION ALL SELECT folders.id, folders.name, 0 AS filesize, folders.updated, folders.deleted, FROM folders ORDER BY 8, 2;

SOURCE

the problemnow is that: [Err] ERROR: cannot delete from view "folders_contents" DETAIL: Views containing UNION, INTERSECT, or EXCEPT are not automatically updatable.

Collectives™ on Stack Overflow

Postgres alter view add column

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related