PostgreSQL: relation would be inherited from more than once

Question

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

• Laurenz Albe: 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.
• user1822: Please edit your question and add the output of select version()

Answer 1

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

2. 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.

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