11

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.

Matt
  • 121
  • 1
  • 5
PS1
  • 179
  • 1
  • 1
  • 7
  • If the tables have the same structure use `union` – Clodoaldo Neto Jun 09 '13 at 10:53
  • 1
    You could use `union all`, filling columns with `null` as needed. What are the table definitions? – Denis de Bernardy Jun 09 '13 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 Jun 09 '13 at 20:56
  • @PS1: A table definition is more than just the column names. [Consider this comment](http://stackoverflow.com/questions/17025078/postgresql-show-data-of-previous-year-current-years-week#comment24613431_17025078) – Erwin Brandstetter Jun 10 '13 at 16:51
  • 1
    This sounds more like a question about the SQL client, not about Postgres. Maybe try a different SQL client? –  Nov 20 '15 at 18:15

4 Answers4

11

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.

Ibrahim Dauda
  • 645
  • 8
  • 19
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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
3

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

James
  • 31
  • 3
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

IlGala
  • 3,331
  • 4
  • 35
  • 49
ra1
  • 1
  • 1