Skip site navigation (1) Skip section navigation (2)

Re: How to add an INHERITS to an already populated table

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: David Pradier <dpradier(at)apartia(dot)fr>, pgsql-general(at)postgresql(dot)org
Subject: Re: How to add an INHERITS to an already populated table
Date: 2005-05-31 12:53:46
Message-ID: Pine.GSO.4.62.0505311649130.16737@ra.sai.msu.su (view raw or flat)
Thread:
Lists: pgsql-general
On Tue, 31 May 2005, Oleg Bartunov wrote:

> look on pg_inherits table and pg_class.relhassubclass.
> More info http://www.pgsql.ru/db/mw/msg.html?mid=2044343
>

example:

create table t (i int4);
create table t1 (i int4);
create table t2 (i int4);

-- mark 't' has children tables
update pg_class set relhassubclass='t' where relname='t';
-- get oid of child table 't1'
select relfilenode from pg_class where relname='t1';
-- get oid of parent table
select relfilenode from pg_class where relname='t';
-- add inheritance t-t1 
insert into pg_inherits values(15769046,15769044,1);
-- get oid of child table 't2'
select relfilenode from pg_class where relname='t2';
-- add inheritance t-t2
insert into pg_inherits values(15769048,15769044,1);

--test
test=# explain analyze select * from t;
                                                   QUERY PLAN 
--------------------------------------------------------------------------------------------------------------
  Result  (cost=0.00..88.20 rows=5820 width=8) (actual time=0.057..0.057 rows=0 loops=1)
    ->  Append  (cost=0.00..88.20 rows=5820 width=8) (actual time=0.044..0.044 rows=0 loops=1)
          ->  Seq Scan on t  (cost=0.00..29.40 rows=1940 width=8) (actual time=0.008..0.008 rows=0 loops=1)
          ->  Seq Scan on t1 t  (cost=0.00..29.40 rows=1940 width=8) (actual time=0.007..0.007 rows=0 loops=1)
          ->  Seq Scan on t2 t  (cost=0.00..29.40 rows=1940 width=8) (actual time=0.006..0.006 rows=0 loops=1)
  Total runtime: 0.171 ms
(6 rows)

-- check if alter table works
alter table t add column x real;
test=# \d t
        Table "public.t"
  Column |  Type   | Modifiers 
--------+---------+-----------
  i      | integer |
  x      | real    |

test=# \d t1
       Table "public.t1"
  Column |  Type   | Modifiers 
--------+---------+-----------
  i      | integer |
  x      | real    | 
Inherits: t

test=# \d t2
       Table "public.t2"
  Column |  Type   | Modifiers 
--------+---------+-----------
  i      | integer |
  x      | real    | 
Inherits: t



>

> On Tue, 31 May 2005, Richard Huxton wrote:
>
>> David Pradier wrote:
>>> Hi everybody,
>>> 
>>> is it possible to add some inheritance lively, without doing a
>>> dump/restore ?
>> 
>> Not AFAIK. Easiest solution is probably to script some ALTER TABLE ADD 
>> COLUMN commands.
>> 
>> 
>
> 	Regards,
> 		Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>              http://www.postgresql.org/docs/faq
>

 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

In response to

Responses

pgsql-general by date

Next:From: FERREIRA, William (COFRAMI)Date: 2005-05-31 13:12:26
Subject: database auto-commit
Previous:From: Oleg BartunovDate: 2005-05-31 12:33:39
Subject: Re: How to add an INHERITS to an already populated table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group