CODEWITHSUNDEEP
postgresqlview
arual
arual

Reputation: 279

Postgres alter view add column

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 :)

Upvotes: 18

Views: 36311

Answers (1)

Houari
Houari

Reputation: 5631

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

Upvotes: 31

Related Questions