CODEWITHSUNDEEP
postgresqlpostgresql-9.1
CircleCode
CircleCode

Reputation: 107

move columns from a table to its parent

In postgresql 9.1, I've tableB inheriting from tableA.

There are some columns in tableB, and no column in tableA.

I want to move columns from tableB to tableA without dumping and reimporting rows from TableB… is it possible ? (I precise I have no rows at all directly in tableA).

Upvotes: 1

Views: 574

Answers (1)

bma
bma

Reputation: 9796

You can alter the parent table and add the same columns as what exists in the child table(s). Any columns that already exist in the child tables with the same data type will not propagate to the children, but any columns that you create in the parent that do not already exist in the child will get created in the child tables.

-- Create parent table "p"
create table p();

-- Create child table "c"
create table c (id int, val text, val2 text) inherits (p);

-- Add the columns to the parent
-- which already exist in the child table "c".
alter table p add val text;
alter table p add val2 text;

-- Add a column that does not exist in table "c"
alter table p add val_xxx bigint;


\d p
       Table "public.p"
 Column  |  Type  | Modifiers 
---------+--------+-----------
 val     | text   | 
 val2    | text   | 
 val_xxx | bigint | 
Number of child tables: 1 (Use \d+ to list them.)

Edited to show the results of follow-up question about what happens to the row(s) in the inherited tables if one of the columns is removed from the parent and child table.

begin;
-- Drop the "val" column from the parent table
alter table p drop column val; 

-- The "val" colum no longer exists in the parent table.
select * from only p;
 val2 | val_xxx 
------+---------
(0 rows)

-- The "val" column still exists in the inherited (child) table
select * from c;
 id | val | val2 | val_xxx 
----+-----+------+---------
  1 | aaa | bbb  |     999

-- Drop the column from the inherited (child) table
alter table c drop column val;

-- The "val" column no longer exists in the child table
select * from c;
 id | val2 | val_xxx 
----+------+---------
  1 | bbb  |     999

rollback;

Upvotes: 1

Related Questions