CODEWITHSUNDEEP
sqlpostgresqlddl
piokuc
piokuc

Reputation: 26194

Modifying the schema of a table to make it inherit from another one without recreating the table or reinserting the data

I have several tables in my PostgreSQL database that have a couple of common columns. I thought it would be a good idea if I move those common columns into a new table and made the tables inherit from this new table. For example:

create table foo_bar (
    code varchar(9) primary key,
    name varchar,
    bar integer
);

After refactoring:

create table foo (
    code varchar(9) primary key,
    name varchar
);

create table foo_bar (
    bar integer
) inherits (foo);

The problem is, I have lots of data in foo_bar, as well as many views that refer to this table.

Is it possible to alter definition of foo_bar to achieve the above change without dropping the data in the table?

Upvotes: 2

Views: 91

Answers (2)

e4c5
e4c5

Reputation: 53774

It may or may not be a good idea. Database design isn't exactly object oriented programming. There are some caveats.

A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint.

And there are few more issues described at that link which you need to be aware of.

If you really want to go ahead with this, how to alter the table is given in Laurenz Albe's answer.

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 247235

CREATE TABLE foo (
    code varchar(9),
    name varchar
);

ALTER TABLE foo_bar INHERIT foo;

Upvotes: 1

Related Questions