Postgresql Updating Non-Labeled View Column

Question

I accidentally created a view with a concatenated column and did not alias the column on creation. I would like to fix the alias, but I'm not sure how to alter the view's column since the anonymous reference is not working. Is there a proper way to alter the views column name?

Query to create the view:

CREATE VIEW emp_full_name AS
    SELECT first_name || ' ' || last_name
    FROM employee

Querying the view:

?column?
John Doe
Jane Doe
....

Attempt to update column with new name:

ALTER VIEW emp_full_name RENAME ?column? TO full_name

Error:

LINE 1: ALTER VIEW emp_full_name RENAME ?column? TO full_name

Gordon Linoff · Accepted Answer · 2020-04-08 01:28:07Z

1

You can redefine the entire view:

CREATE OR REPLACE VIEW emp_full_name as
    SELECT (first_name || ' ' || last_name) as full_name
    FROM employee;

edited Apr 8, 2020 at 1:28

answered Apr 8, 2020 at 0:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

cphill Over a year ago

I ran your query above and received the following error ERROR: syntax error at or near "AS" LINE 1: ALTER VIEW emp_full_name AS

Gordon Linoff Over a year ago

@cphill . . . as is not used for alter view. It is used for create view.

cphill Over a year ago

Ahh thanks for catching that. Still seeing an error with the edited answer ERROR: syntax error at or near "SELECT" LINE 2: SELECT (first_name || ' ' || last_name) as full_name

Collectives™ on Stack Overflow

Postgresql Updating Non-Labeled View Column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related