Обсуждение: 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
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:Your case may be very special, but offhand, this sounds like a very
> Hi all,
>
> I want to add some common columns to all of my tables.
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.
Cheers,
David.
Jun
--
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
On Sat, Aug 1, 2009 at 9:42 PM, David Fetter <david@fetter.org> wrote:
On Sat, Aug 01, 2009 at 08:10:02PM -0700, Jun Yang wrote:This sounds like you're working your way up to some kind of logging
> 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.
system :)
Check out the tablelog <http://pgfoundry.org/projects/tablelog/>
Very interesting. Thanks a lot for the pointer.
Cheers,
David.
Jun
--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