0

Say there is a PostgreSQL parent table files that is empty; it's 'abstract'. And then there are tables that inherit from files that contain data: red-files, green-files, blue-files.

I can select from all three by selecting from files*, but how can I select from both red-files and green-files only?

That is, if the query would retrieve 3 rows from red-files and 2 rows from green-files, then the combined query I'm looking for would show 5 rows.

jensph
  • 763
  • 1
  • 10
  • 22

1 Answers1

1

You can use UNION ALL unless there is a field to join its.

CREATE TABLE files (
    name            text,
    size            int
);

CREATE TABLE redfiles (
    id           char(2)
) INHERITS (files);


CREATE TABLE bluefiles (
    id           char(2)
) INHERITS (files);

insert into redfiles (name, id, size) values ('file1','aa', 1024);
insert into redfiles (name, id, size) values ('file2','bb', 2048);
insert into bluefiles (name, id, size) values ('file3','xx', 1024);
insert into bluefiles (name, id, size) values ('file4','yy', 1526);

select * from files;

name  | size
:---- | ---:
file1 | 1024
file2 | 2048
file3 | 1024
file4 | 1526

with MyFiles as
(
    select * from redfiles
    union all
    select * from bluefiles
) 
select *
from   MyFiles
where  size = 1024;

name  | size | id
:---- | ---: | :-
file1 | 1024 | aa
file3 | 1024 | xx

dbfiddle here

McNets
  • 10,352
  • 3
  • 32
  • 61
  • See the edit in the original question -- hopefully now my question is clearer. – jensph Apr 07 '17 at 19:14
  • You construct each table's query separately and then apply a union. So, there's no way to apply a single query to the tables together? Say `files` has a column name `size`. If I want a `where size > 500` to apply to all tables, it'd still need to be written for each table's individual query? – jensph Apr 07 '17 at 19:30
  • Is there any field that allows to join both tables? – McNets Apr 07 '17 at 22:10
  • This is what I was looking for: `with t as ( select * from redfiles union all select * from bluefiles ) select * from t where size > 500`. Please add to your answer. – jensph Apr 07 '17 at 22:12
  • is `size` one attribute of your table schema? – McNets Apr 07 '17 at 22:14
  • `size` is a column in the `files` parent table, which I mentioned in the previous comment to better describe what I hoped to find; namely, a way to aggregate the tables and then have a single query qualifier. Using a `with` along with your suggested `union all` is what I wanted. – jensph Apr 07 '17 at 22:17