21

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?

Improve this question

3 Answers 3

Reset to default
37

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.)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

10 
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
Improve this answer

1 Comment

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.
8

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.

Improve this answer

4 Comments

Thanks, thats great. Bit of an ugly job all round, so I'm happy with something that works, which this does!
When a query gets ugly in PostgreSQL, more often than not, a more elegant solution is around the corner. ;)
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.
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.