CODEWITHSUNDEEP
postgresqlviewmaterialized-views
Arnaud Denoyelle
Arnaud Denoyelle

Reputation: 31235

Rename column in a view : bug or feature?

I made a mistake on the name of a column in a Postgresql 9.4 materialized view.

I looked at the documentation, the syntax is : 

ALTER MATERIALIZED VIEW my_view_name
RENAME COLUMN old_name 
TO new_name;

It worked fine.

Then I had the same typo in a non-materialized view. I made a copy paste of the request, forgot to remove the key-word MATERIALIZED and... it worked!

Weirder, if I try : 

ALTER VIEW my_view_name
RENAME COLUMN old_name 
TO new_name;

It does not work, I get a syntax error!

In other words : it seems that I can only change the column name of a normal view by using the syntax from the materialized view.

Is that a bug or a feature?

Steps to reproduce :

1) Create a simple table

CREATE TABLE films (
  code        char(5) CONSTRAINT firstkey PRIMARY KEY,
  title       varchar(40) NOT NULL
);

2) Create a view as a simple select : 

CREATE VIEW view_films AS SELECT * FROM films;

3) Try to rename a column : 

ALTER VIEW view_films RENAME COLUMN title TO new_title;

ERROR: syntax error at or near "COLUMN"

4) Try the same but with the keyword MATERIALIZED : 

ALTER MATERIALIZED VIEW view_films RENAME COLUMN title TO new_title;

ALTER TABLE

Upvotes: 2

Views: 5166

Answers (1)

Rahul
Rahul

Reputation: 77876

I am not sure with MATERIALIZED VIEW but for a normal view; the view doesn't store any data rather it's just a saved SELECT query and upon saying select * from view_name it does nothing but running the underlying SELECT statement against the target table.

So the ALTER statement to change column name should be fired against the table on which the view is created and not on the view; and it should work just fine. Try below:

CREATE TABLE films (
  code        char(5) CONSTRAINT firstkey PRIMARY KEY,
  title       varchar(40) NOT NULL
);

CREATE VIEW view_films AS SELECT * FROM films;

ALTER TABLE films RENAME COLUMN title TO new_title;

For a MATERIALIZED VIEW renaming column will be possible cause, it does store data.

On your comment: you should re-create the view definition after ALTERING the table schema. Check this fiddle http://sqlfiddle.com/#!15/9ebe1/1

Upvotes: 3

Related Questions