How can I inherit comments when using CREATE TABLE INHERITS

I have a table basetemplate created using:

CREATE TABLE basetemplate (
    time_of_creation timestamp with time zone,
);

Now I want to create table1, table2 with different fields, but both containing time_of_creation field. I tried using CREATE TABLE LIKE and CREATE TABLE INHERITS. Of this CREATE TABLE INHERITS seemed to better match my requirement, as I could create the new table with the new table's fields specified in the same query. But for the other method I would have to use ALTER TABLE query to add my new table's specific fields. CREATE TABLE INHERITS inherited the fields, its constraint; but not its comments.
My question is, whether this is the best way to fulfil my need and also is there a way to inherit comments also, when using CREATE TABLE INHERITS.

Solution

CREATE TABLE INHERITS is not suitable for just duplicating table definitions. The actual data of the tables are linked, so if that is not what you want, then do not use table inheritance, use CREATE TABLE ... LIKE or CREATE TABLE ... OF TYPE, then add fields with ALTER TABLE commands. If that's too cumbersome it might be a sign you should just be defining a new table, or perhaps using a composite type or a domain.

It seems like you're trying to apply OOP polymorphic inheritance to SQL. Not a great idea. In this case, your best bet is almost certainly just to live with repeating the common field. In more complex cases you can create a DOMAIN, or create a composite type with CREATE TYPE, but in this case that's unnecessary; just repeat the field in each table.

Here's what happens if you use INHERITS then insert a row into the base table:

regress=> CREATE TABLE basetemplate (
    time_of_creation timestamp with time zone
);
CREATE TABLE
regress=> CREATE TABLE table1 (col1 text) INHERITS (basetemplate);
CREATE TABLE
regress=> INSERT INTO table1(col1, time_of_creation) VALUES ('a', current_timestamp);
INSERT 0 1
regress=> SELECT * FROM table1 ;
       time_of_creation        | col1 
-------------------------------+------
 2012-11-08 16:23:35.645734+08 | a
(1 row)

fine so far, but now:

regress=> select * from basetemplate ;
       time_of_creation        
-------------------------------
 2012-11-08 16:23:35.645734+08
(1 row)

This probably isn't what you want.

It doesn't actually do any harm; there isn't any overhead to inserting rows into table1 because of the inheritance. You'll find that it makes doing some management tasks more complicated later, though, and it'll complicate things like table partitioning. Overall I don't recommend using inheritance unless you need to.