Query parent table and get child tables columns

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    |

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.

StackExchange Database Administrators Q#94050, answer score: 8