I have a simple view which concatenates two strings. In postgreSQL I have not been able to name to created column or rename it afterwards. The output from postgreSQL has the line d.dirname || f.fname AS "?column?". It is possible to use this as input to recreate the view. However, any name other than "?column?" is rejected. alter table rename doesn't work either.

Is there a way to name the column?

\d+ fpathv
                            View "public.fpathv"
  Column  |  Type  | Collation | Nullable | Default | Storage  | Description 
----------+--------+-----------+----------+---------+----------+-------------
 fileid   | bigint |           |          |         | plain    | 
 dirid    | bigint |           |          |         | plain    | 
 ?column? | text   |           |          |         | extended | 
View definition:
 SELECT f.fileid,
    d.dirid,
    d.dirname || f.fname AS "?column?"
   FROM files f,
    dirs d
  WHERE f.dirid = d.dirid;

I have a simple view which concatenates two strings. In postgreSQL I have not been able to name to created column or rename it afterwards. The output from postgreSQL has the line d.dirname || f.fname AS "?column?". It is possible to use this as input to recreate the view. However, any name other than "?column?" is rejected. alter table rename doesn't work either.

Is there a way to name the column?

\d+ fpathv
                            View "public.fpathv"
  Column  |  Type  | Collation | Nullable | Default | Storage  | Description 
----------+--------+-----------+----------+---------+----------+-------------
 fileid   | bigint |           |          |         | plain    | 
 dirid    | bigint |           |          |         | plain    | 
 ?column? | text   |           |          |         | extended | 
View definition:
 SELECT f.fileid,
    d.dirid,
    d.dirname || f.fname AS "?column?"
   FROM files f,
    dirs d
  WHERE f.dirid = d.dirid;

• postgresql
• database-view

• 3 1) The "?column?" came about because when you originally created the view you must have had just ... d.dirname || f.fname ..., in other words no explicit AS. Postgres creates "?column?" in that situation. 2) The only way you change this is DROP the view and recreate it using an explicit alias e.g.d.dirname || f.fname AS dir_file_name. – Adrian Klaver Commented Jan 31 at 23:05
• You shouldn't need to drop and recreate just to change the column name. Redefining it with create or replace view won't work for the renaming and it might ask you to alter [view|table]..rename column instead, which is what you should be able to do. Can you show the query you tried and the error you got? – Zegarek Commented Feb 1 at 12:38

2 Answers 2

You have to drop and re-create the view. There are two ways to define the view:

CREATE VIEW v (col1, ...) AS SELECT x || y, ...

or

CREATE VIEW v AS SELECT x || y AS col1, ...

In Postgres version 13 and above, you should be able to rename the column both via alter table and alter view:
_{demo at db<>fiddle}

ALTER VIEW IF EXISTS fpathv RENAME COLUMN "?column?" TO new_column_name;
ALTER TABLE IF EXISTS fpathv RENAME COLUMN new_column_name TO newer_column_name;
select*from fpathv;

Prior to version 13, you can still do that with alter table..rename column but the syntax for alter view isn't there yet. Note that this doesn't cascade to dependent views:

create view view2 as select*from fpathv;
ALTER VIEW IF EXISTS fpathv RENAME COLUMN new_column_name TO newer_column_name;
select*from view2;
select*from fpathv;