Create view in postgres sql with comments

Question

I create a view in postgres sql with sql statement

CREATE OR REPLACE VIEW  {ViewName} as 
Select
.....

I am asking is there any way to create comments for columns in the view. After the view is created, it generates an error when a comment is added to a column :

ERROR: "{ViewName}" is not a table, composite type, or foreign table.

please share the full statement, or at least show example of comments youwant — Vao Tsun
– Vao Tsun, Commented Jun 14, 2017 at 7:02
CREATE OR REPLACE VIEW {ViewName} as Select column1, column2, .... — IvoAtanasov
– IvoAtanasov, Commented Jun 14, 2017 at 7:20
That can't be the real statement. ({ViewName} is an invalid identifier to begin with) — user330315
– user330315, Commented Jun 14, 2017 at 7:21
CREATE OR REPLACE VIEW {ViewName} as Select column1, column2, .... And I want columns with comments . Error is generated when I manually try to set a comment after clicking column properties. I want when someone is looking at the view to see a comments for each columns as it is a regular table.Every table in out database consist of columns with comments and I want to preserve this approach. — IvoAtanasov
– IvoAtanasov, Commented Jun 14, 2017 at 7:28

user330315 · Accepted Answer · 2017-06-14 07:20:17Z

13

To define a comment on a column (or a view) use comment on:

create view some_view
as
select x as col1, y as col2, z as col3
from some_table;

Then:

comment on view some_view is 'Some View';
comment on column some_view.col1 is 'Originally column X';

answered Jun 14, 2017 at 7:20

user330315

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

2 Comments

IvoAtanasov Over a year ago

OK I manage to add comment on the view itself but I get an error ERROR: cross-database references are not implemented: "some_view.col1"

IvoAtanasov Over a year ago

It's working I just misspell my column name , sorry. Thanks @a_horse_with_no_name

NutCracker · Accepted Answer · 2019-10-16 22:43:38Z

1

Use a dummy select statement.

select 'general information for the use of the comments' as c1;

It's clunky but the only way I can find to embed comments. For larger items, I create a commentary in a header.

with query_header as (
    select 'purpose - statement' as c1,
    select 'revision - revised 16 Oct 2019 by WDH' as c2
    select 'owner - contact details' as c3
    select 'lines 234-312 to declutter orders with no valid partnmber' as c4
    select 'join on itemtable changed to left join 23July2018 by WDH' as c5
)

edited Oct 16, 2019 at 22:43

NutCracker

12.4k5 gold badges51 silver badges74 bronze badges

answered Oct 16, 2019 at 21:12

Bill

112 bronze badges

Collectives™ on Stack Overflow

Create view in postgres sql with comments

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related