Setting the comment of a column to that of another column in Postgresql

Question

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.

Answer 1

The next thing to know is how to obtain the table oid. I think that using this as part of comment on will not work, as you suspect.

    postgres=# create table comtest1 (id int, val varchar);
    CREATE TABLE
    postgres=# insert into comtest1 values (1,'a');
    INSERT 0 1
    postgres=# select distinct tableoid from comtest1;
     tableoid
    ----------
        32792
    (1 row)

    postgres=# comment on column comtest1.id is 'Identifier Number One';
    COMMENT
    postgres=# select col_description(32792,1);
        col_description
    -----------------------
     Identifier Number One
    (1 row)

Anyhow, I whipped up a quick plpgsql function to copy comments from one table/column pair to another. You have to createlang plpgsql on the database and use it like this:

    Copy the comment on the first column of table comtest1 to the id 
    column of the table comtest2. Yes, it should be improved but 
    that's left as work for the reader.

    postgres=# select copy_comment('comtest1',1,'comtest2','id');
     copy_comment
    --------------
                1
    (1 row)

CREATE OR REPLACE FUNCTION copy_comment(varchar,int,varchar,varchar) RETURNS int AS $PROC$
DECLARE
        src_tbl ALIAS FOR $1;
        src_col ALIAS FOR $2;
        dst_tbl ALIAS FOR $3;
        dst_col ALIAS FOR $4;
        row RECORD;
        oid INT;
        comment VARCHAR;
BEGIN
        FOR row IN EXECUTE 'SELECT DISTINCT tableoid FROM ' || quote_ident(src_tbl) LOOP
                oid := row.tableoid;
        END LOOP;

        FOR row IN EXECUTE 'SELECT col_description(' || quote_literal(oid) || ',' || quote_literal(src_col) || ')' LOOP
                comment := row.col_description;
        END LOOP;

        EXECUTE 'COMMENT ON COLUMN ' || quote_ident(dst_tbl) || '.' || quote_ident(dst_col) || ' IS ' || quote_literal(comment);

        RETURN 1;
END;
$PROC$ LANGUAGE plpgsql;

Answer 2

You can retrieve comments on columns using the system function col_description(table_oid, column_number). See this page for further details.