Combine multiple SELECT statements

Question

I've used Excel to generate numerous SELECT statements from a list of the schema names from a database with a large number of identical schemas:

select result from foo.table limit 1;
select result from bar.table limit 1;
select result from doo.table limit 1;

(foo, bar & doo are examples of my schemas, there are hundreds in reality).

Each SELECT will return only one result. I simply want one column result with as many rows as there are schemas. I can then copy this back into Excel against the schema names.

When I run the query above I get 1 row, with the others being discarded:

Query result with 1 row discarded.

Query result with 1 row discarded.

Total query runtime: 40 ms.
1 row retrieved.

I have tried using UNION ALL, but the limit 1 I am using to ensure one row only is returned from each schema table appears to prevent this from working.

How can I either prevent the other rows from being discarded, or write a query that will return the values I need (two columns - schema_name, result - one row for each schema) in a more efficient way?

Solution 1

Wrap individual sub-statements in parenthesis to make the syntax unambiguous:

(SELECT result FROM tbl1 LIMIT 1)
UNION ALL
(SELECT result FROM tbl2 LIMIT 1)

The manual about UNION is very clear on the matter:

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR UPDATE, or FOR SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

Solution 2

Wrapping in a subquery will get around it, but it gets a bit ugly.

SELECT result FROM (select 'a'::text AS result from foo limit 1) a
UNION ALL
SELECT result FROM (select 'b'::text AS result from bar limit 1) b

UPDATE

See Erwin's response. It is better.

Solution 3

create view my_data1 
AS
with data as
(
    select student_id,sum(marks) as total_marks 
    from   marks_marks 
    group by 1
) , 
data1 as
(
    select id, name 
    from   students_class
), 
data2 as
(
    select applicant_name,
           id,
           class_name 
    from   students_students
)
select data2.applicant_name , 
       data1.name as class_name ,  
       data.total_marks 
from   data2 
join   data1  on data1.id = data2.class_name 
join   data   on data.student_id = data2.id



select * from my_data1

Comments

[Andrew M]

Thanks, thats great. Bit of an ugly job all round, so I'm happy with something that works, which this does!

[Erwin Brandstetter]

When a query gets ugly in PostgreSQL, more often than not, a more elegant solution is around the corner. ;)

[Glenn]

If it bothers you that the an accepted answer from earlier in the day was improved based on you additions, I'd delete it. But I can't seem to delete an accept answer.

[Erwin Brandstetter]

You can always edit. I did not suggest to delete. If you become convinced that my answer is better, just point to it (and possibly explain why). You are not adding value for anyone by copying it, and it is unfair towards me.

[Toby Speight]

Thank you for this code snippet, which might provide some limited short-term help. A proper explanation would greatly improve its long-term value by showing why this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please edit your answer to add some explanation, including the assumptions you've made.