Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx, 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@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux