Postgres: select data from parent table and all child tables

Question

I have a parent table and several child tables whose structure differs a bit from parent one and from each other. can I assemble a query that gets all the data from parent table and all child tables with all additional columns?

Answer 1

Use a proper schema and use INHERITS to tell PostgreSQL about the structure:

CREATE TABLE foo(x int);

CREATE TABLE bar(y int) INHERITS (foo); -- <===

INSERT INTO foo(x) VALUES(1); -- one record
INSERT INTO bar(x,y) VALUES(2,3); -- one record

SELECT * FROM foo; -- two results

SELECT * FROM ONLY foo; -- one result, see ONLY

http://www.postgresql.org/docs/current/interactive/ddl-inherit.html

Answer 2

It seems like what you'd need here is to use the left outer join. Again, not an ideal solution and potentially a change in structure to the database would be better, but still, here it is:

CREATE TABLE foo(x int);

CREATE TABLE bar(y int) INHERITS (foo);

INSERT INTO foo(x) VALUES(1);
INSERT INTO bar(x,y) VALUES(2,3);

SELECT foo.*, bar.y 
FROM foo LEFT OUTER JOIN bar ON (foo.x = bar.x);

The entry just in the parent has null for y, which will have to be taken into account, but it does pull out all of the data. The downside here is that you need to specifically name the extra columns from child tables. (Though I find it best to avoid using * in queries that are going into code, anyway.)

If you really want to not have to name the columns in the child table, there's an article about how to select all except certain columns here. You can figure out how to put the two together.

EDIT:

In case y can be set to null and it is important to differentiate between what is a legitimate bar y null and a non ligitimate foo y null, you can try this instead:

SELECT 'foo' AS from_table, *, NULL::int AS y
FROM ONLY foo

UNION

SELECT 'bar' AS from_table, *
FROM bar;

Then on the code end you can use the from table column to process what came from where and tell which null y values are legitimate and which are not.