0

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
  • 1
    Are you sure that this is regular PostgreSQL? PostgreSQL does allow multiple inheritance, and such an ungrammatical error message would never have made it through the peer review process. – Laurenz Albe Jul 2 at 5:28
  • Please edit your question and add the output of select version() – a_horse_with_no_name Jul 2 at 6:21
0

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.