CODEWITHSUNDEEP
postgresqlpgadmin
PS1
PS1

Reputation: 341

Outputting results from multiple sql queries in postgresql

I have postgresql-9.2 installed on my local machine (running windows 7) and I am also the administrator. I am using the Query Tool of pgAdmin III to query my database. My problem is as follows:

Say I have two tables Table_A and Table_B with different number of columns. Also, say I have following two very simple queries:

select * from Table_A;
select * from Table_B;

I want to run both these queries and see the output from both of them together. I dont mind if I see the output in the GUI or in a file.

I also tried the copy command and outputting to a csv. But instead of appending to the file it overwrites it. So, I always end up with the results from query 2 only. The same thing happens with the GUI.

It is really annoying to comment one query, run the another, output to two different files and then merge those two files together.

Upvotes: 14

Views: 29660

Answers (4)

James
James

Reputation: 51

The PSQL tool in the top menu under TOOLS (pgadmin4) gives results of multiple queries, unlike the query tool. In the PSQL command line tool, you can enter two or more queries separated by a semicolon and you'll get the results of each query displayed. The downside is that this is a command line tool so the results are not ideal if you have a lot of data. I use this when I have a lot of updates to string together and I want to see the number of rows updated in each. This would work well for select queries with small results. psql tool

Upvotes: 5

Ibrahim Dauda
Ibrahim Dauda

Reputation: 655

This is not currently supported by PostgreSQL - from the docs (http://www.postgresql.org/docs/9.4/interactive/libpq-exec.html):

The command string can include multiple SQL commands (separated by semicolons). Multiple queries sent in a single PQexec call are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the query string to divide it into multiple transactions. Note however that the returned PGresult structure describes only the result of the last command executed from the string. Should one of the commands fail, processing of the string stops with it and the returned PGresult describes the error condition.

Upvotes: 12

ra1
ra1

Reputation: 1

You can use UNION ALL, but you need to make sure each sub query has the same number of columns.

SELECT 'a', 'b'
UNION ALL
SELECT 'c' ;

won't work.

SELECT 'a', 'b'
UNION ALL
SELECT 'c', 'd'

will work

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657922

Your problem does not depend on the client.

Assuming all columns to be of type text, try this query:

SELECT col_a AS col_ac, col_b AS col_bd
      ,NULL::text AS col_e, NULL::text AS col_f
FROM   table_a

UNION  ALL
SELECT col_c, col_d, col_e, col_f
FROM   table_b;

Column names and data tapes are defined by the first branch of a UNION SELECT. The rest has to fall in line.

Upvotes: 4

Related Questions