Setting the comment of a column to that of another column in Postgresql
- by dland
Suppose I create a table in Postgresql with a comment on a column:
create table t1 (
c1 varchar(10)
);
comment on column t1.c1 is 'foo';
Some time later, I decide to add another column:
alter table t1 add column c2 varchar(20);
I want to look up the comment contents of the first column, and associate with the new column:
select comment_text from (what?) where table_name = 't1' and column_name = 'c1'
The (what?) is going to be a system table, but after having looked around in pgAdmin and searching on the web I haven't learnt its name.
Ideally I'd like to be able to:
comment on column t1.c1 is (select ...);
but I have a feeling that's stretching things a bit far. Thanks for any ideas.
Update: based on the suggestions I received here, I wound up writing a program to automate the task of transferring comments, as part of a larger process of changing the datatype of a Postgresql column. You can read about that on my blog.