Re: Can I add a super table to existing tables?

> On Sat, Aug 1, 2009 at 6:49 PM, Andy Colson <andy(at)squeakycode(dot)net
> <mailto:andy(at)squeakycode(dot)net>> wrote:
>
> Andy Colson wrote:
>
> Jun Yang wrote:
>
> Hi all,
>
> I want to add some common columns to all of my tables. One
> way I
> think would be to add a super table that contains the common
> columns
> to all tables. But is there a way to add a super table to
> existing
> tables for them to inherit from?
>
> Thanks!
>
> Jun
>
>
> as long as the parent and child has the same table struct, yes.
>
> use:
>
> alter table child inherit newparent;
>
> -Andy
>
>
> err... sorry, let me fix that: the parent must have a common subset
> of all the children. (The children can have extras the parent does
> not, but not visa versa)
>
Jun Yang wrote:
> Thanks a lot for your reply, Andy!
>
> That means no then because my child tables are not like the parent at
> all. If the parent has a subset of child's columns, what does that mean
> because I thought the whole point of inheritance is so that child tables
> don't need to define common columns repeatedly using inheriting them
> from the parent.
>
>
> Jun

Please keep the group on the list.

In the docs, you can see that yes, you are correct, if setup from the beginning, the children dont need the parent fields.

http://www.postgresql.org/docs/8.4/interactive/ddl-inherit.html

-- the parent
CREATE TABLE cities (
name text,
population float,
altitude int -- in feet
);

-- the child
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);

But you're doing it after the fact. I tried it out, it doenst work:

andy=# create table capitals (state varchar(2));
CREATE TABLE
Time: 17.754 ms
andy=# create table cities(name text);
CREATE TABLE
Time: 1.971 ms
andy=# alter table capitals INHERIT cities;
ERROR: child table is missing column "name"
andy=#

-Andy