Query parent table and get child tables columns

Question

I have two tables, one inherits the other:

CREATE TABLE parent (
    col1 INTEGER
);

CREATE TABLE child (
    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    |

Answer 1

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!):

SELECT * FROM ONLY parent NATURAL FULL JOIN child;

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

Then:

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.

Answer 2

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;