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

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

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

> On Sat, Aug 1, 2009 at 6:49 PM, Andy Colson <andy@squeakycode.net
> <mailto:andy@squeakycode.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

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
extrasthe parent does not, but not visa versa) 

-Andy

On Sat, Aug 01, 2009 at 06:28:54PM -0700, Jun Yang wrote:
> Hi all,
>
> I want to add some common columns to all of my tables.

Your case may be very special, but offhand, this sounds like a very
bad idea.  What task is it you're trying to accomplish?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Jun Yang wrote:
> On Sat, Aug 1, 2009 at 7:53 PM, David Fetter <david@fetter.org
> <mailto:david@fetter.org>> wrote:
>
>     On Sat, Aug 01, 2009 at 06:28:54PM -0700, Jun Yang wrote:
>     > Hi all,
>     >
>     > I want to add some common columns to all of my tables.
>
>     Your case may be very special, but offhand, this sounds like a very
>     bad idea.  What task is it you're trying to accomplish?
>
>
> Very simple.  I have columns update_by and updated_at that I want
> every table to have but don't want to add them one by one.



for each $name in (select table_name from information_schema.tables
where table_schema='public') do
    alter table $name add column update_by oid, updated_at timestamp;


(no, thats not SQL, its pseudo-code, do this in whatever sort of
scripting or programming language you usually use)


like, in bash scripting...

    for $name in (`psql -t -c "select table_name from
information_schema.tables where table_schema='public'") do
       psql -c "alter table $name add column update_by integer
references users(id), updated_at timestamp default now()"
       done;

of course, you'd be better off doing this in perl, python, php, or
whatever you normally program in so that you weren't launching so many
seperate connections.

On Sat, Aug 01, 2009 at 08:10:02PM -0700, Jun Yang wrote:
> On Sat, Aug 1, 2009 at 7:53 PM, David Fetter <david@fetter.org> wrote:
> > On Sat, Aug 01, 2009 at 06:28:54PM -0700, Jun Yang wrote:
> > > Hi all,
> > >
> > > I want to add some common columns to all of my tables.
> >
> > Your case may be very special, but offhand, this sounds like a
> > very bad idea.  What task is it you're trying to accomplish?
>
> Very simple.  I have columns update_by and updated_at that I want
> every table to have but don't want to add them one by one.

This sounds like you're working your way up to some kind of logging
system :)

Check out the tablelog <http://pgfoundry.org/projects/tablelog/>

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate