Outputting results from multiple sql queries in postgresql

Question

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.

You could use union all, filling columns with null as needed. What are the table definitions? — Denis de Bernardy
– Denis de Bernardy, Commented Jun 9, 2013 at 11:19
@Denis -- Say these are the table structures: Table_A(col_A, col_B); Table_B(col_c, col_D, col_E, col_F) — PS1
– PS1, Commented Jun 9, 2013 at 20:56
@PS1: A table definition is more than just the column names. Consider this comment — Erwin Brandstetter
– Erwin Brandstetter, Commented Jun 10, 2013 at 16:51
This sounds more like a question about the SQL client, not about Postgres. Maybe try a different SQL client? — user330315
– user330315, Commented Nov 20, 2015 at 18:15

Ibrahim Dauda · Accepted Answer · 2015-12-30 03:45:35Z

12

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.

answered Dec 30, 2015 at 3:45

Ibrahim Dauda

6558 silver badges19 bronze badges

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

Comments

James · Accepted Answer · 2022-06-16 20:19:10Z

5

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

edited Jun 16, 2022 at 20:19

answered Jun 16, 2022 at 19:28

James

511 silver badge3 bronze badges

Comments

Erwin Brandstetter · Accepted Answer · 2013-06-10 16:55:09Z

4

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.

answered Jun 10, 2013 at 16:55

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Comments

IlGala · Accepted Answer · 2015-11-20 18:13:54Z

0

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

edited Nov 20, 2015 at 18:13

IlGala

3,4494 gold badges42 silver badges53 bronze badges

answered Nov 20, 2015 at 17:25

ra1

11 bronze badge

Collectives™ on Stack Overflow

Outputting results from multiple sql queries in postgresql

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related