CODEWITHSUNDEEP
sqlpostgresql
Vadim Samokhin
Vadim Samokhin

Reputation: 3456

Postgres: select data from parent table and all child tables

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?

Upvotes: 3

Views: 7701

Answers (3)

Chris Travers
Chris Travers

Reputation: 26464

The simple answer is you can't easily do that. You can't have jagged rows (unlike Informix) and so a simple select approach is not going to work.

You might be able to programmatically create a query that would use row_to_json and a bunch of union statements but that is a lot of work, very complex, and maybe not what you want. it also fails to address cases where columns might have the same name but different meaning in different child tables.

In general, I think you need to rethink your approach here. If you are trying rely on jagged rows, you are going to have a lot of very loose contracts between your application and your database and so you are going to have a lot of potential for unique problems.

The best approach here is to move extra columns off into join tables where you can use left join to retrieve them.

Upvotes: 1

Trevor Young
Trevor Young

Reputation: 545

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.

Upvotes: 1

Frank Heikens
Frank Heikens

Reputation: 127397

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

Upvotes: 3

Related Questions