I have two tables, one inherits the other:

    col1 INTEGER

    col2 INTEGER
) INHERITS( parent );

// Insert some data
INSERT INTO parent( col1 ) VALUES( 1 );
INSERT INTO child( col1, col2 ) VALUES( 2, 2 );

Is there any way to query the parent table, such that it gets all of the columns of the child tables?

This query returns just the parent columns:

SELECT * FROM parent;

| col1 |
| ---- |
| 1    |
| 2    |

My desired query would return all columns in parent and child:

SELECT ...  -- some query, with desired result:

| col1 | col2 |
| ---- | ---- |
| 1    | NULL |
| 2    | 2    |

2 Answers 2


As long as the set of inherited column values is unique across all tables, there is a simple solution with a NATURAL join (one of the rare use cases for this clause!):


Since NATURAL is (per documentation):

... shorthand for a USING list that mentions all columns in the two tables that have the same names.

You get each column only once without explicit SELECT list, and rows from the parent table are extended with NULL values for added columns - exactly the way you desire.

This even works with NULL values in any of the columns.

To also see the origin of each row in the result, let's add another row to your test case:

INSERT INTO child(col1) VALUES(1);


SELECT COALESCE(p.tableoid, c.tableoid)::regclass AS tbl, *
FROM   ONLY parent p NATURAL FULL JOIN child c;

 tbl   | col1 | col2
parent | 1    | NULL
child  | 1    | NULL
child  | 2    | 2

db<>fiddle here - with more test rows
Old sqlfiddle

You can join to multiple child tables this way if all additional column names in all child tables are unique. Else you'll have to spell out an explicit SELECT list and explicit join conditions.

The set of inherited column values has to be unique across all child tables.


I'd say you can't by just querying the parent table. The child inherits from the parent but the parent is unaware of the child. A left outer join should get you the desired answer but that involves both tables

select p.col1, c.col2 from parent p left outer join child c on c.col1 = p.col1;
  • This reads the child table twice and fails for NULL values in col1. You also have to spell out all columns. Jan 2, 2016 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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