PostgreSQL Error – Relation Would Be Inherited More Than Once

postgresqlpostgresql-9.3

Assume I have a schema which initially have several yearly sliced data like: 

schema: land_price
  - land_price_1990
  - land_price_1991
  ...
  - land_price_2010

Now I would like to create a parent table so that I can better manage them. I firstly try 

ALTER TABLE land_price.land_price_2010 INHERIT land_price.land_price_parents;

It works, however, when I try to alter the second table, it fails and returns 

ERROR: relation "land_price_parents" would be inherited from more than once

So in this case, how can I solve this problem?

p.s. Here is the version information: 

select version()
PostgreSQL 9.3.23 on x86_64-unknown-linux-gnu (Ubuntu 9.3.23-2.pgdg14.04+1), compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit

Best Answer

The error indicates, "the table is already inherited with same or other parent table.

Here is an example:

  1. Create three tables Table_A, Table_B & Table_C and insert similar values to all of th three tables.
    CREATE TABLE Table_A
    (
                                            EmpId int,
                                            EName varchar(128)
    );

    INSERT INTO Table_A(EmpId,EName)
    VALUES(1,'Rajesh');
    INSERT INTO Table_A(EmpId,EName)
    VALUES(2,'Ranjan');
    INSERT INTO Table_A(EmpId,EName)
    VALUES(3, 'Alok');

    CREATE TABLE Table_B
    (
                                            EmpId int,
                                            EName varchar(128)
    );

    INSERT INTO Table_B(EmpId,EName)
    VALUES(1,'Rajesh');
    INSERT INTO Table_B(EmpId,EName)
    VALUES(2,'Ranjan');
    INSERT INTO Table_B(EmpId,EName)
    VALUES(3, 'Alok');

    CREATE TABLE Table_C
    (
                                            EmpId int,
                                            EName varchar(128)
    );

    INSERT INTO Table_C(EmpId,EName)
    VALUES(1,'Rajesh');
    INSERT INTO Table_C(EmpId,EName)
    VALUES(2,'Ranjan');
    INSERT INTO Table_C(EmpId,EName)
    VALUES(3, 'Alok');
  1. Then execute ALTER statement to add inheritance. For both alter statements executed successfully.
    ALTER TABLE PUBLIC.Table_A INHERIT PUBLIC.Table_B;

    Result:
    ALTER TABLE
    Query returned successfully in 132 msec.

    ALTER TABLE PUBLIC.Table_A INHERIT PUBLIC.Table_C;

    Result:
    ALTER TABLE
    Query returned successfully in 137 msec.
  1. Now execute the same ALTER statement again and it'll result similar error.
    ERROR:  relation "table_c" would be inherited from more than once
    SQL state: 42P07

You can use following query to find inherited tables.

SELECT
    A.*, c.relname AS child, C.relname AS parent
FROM
    pg_inherits A
INNER JOIN  pg_class AS B ON (A.inhrelid=B.oid)
INNER JOIN pg_class AS C ON (A.inhparent=C.oid);
Related Question