To select from all tables inside the schema in PostgreSQL

Question

Is it possible to select from all tables inside the schema? I got all the table names from

select table_name from information_schema.tables

but I am unable to use it to do my query.

• Erwin Brandstetter: You must define exactly what to select. You cannot retrieve incompatible row structures in a single select.
• Hubbitus: The table structure almost certainly different. So, what exactly do you want to get from all tables? You may use some hints from the question about getting COUNT(*) for all tables: stackoverflow.com/questions/2596670/…

Answer 1

You can't do it directly as one can not write a 'normal' query if the table names are not known (ie. coming from some variable or subquery). But you can build and execute a dynamic SQL statement for this. For example, if you need the column 'name' from every table, you can do the following (inside a PL/pgSQL function):

FOR i IN SELECT table_name 
 FROM information_schema.tables 
 WHERE table_schema = 'your_desired_schema'
LOOP
 sql_string := sql_string || format($$
 -- some whitespace is mandatory here
 UNION
 SELECT name FROM %I
 $,ドル
 i.table_name);
END LOOP;
EXECUTE sql_string;

In this form it won't work however, since you cannot SELECT in plpqsql unless you do it into a variable. You can either create a temporary table for this, loop over the results (in an other FOR loop), or - not using UNION - just return in every iteration, depending on your needs.

And, of course, this presumes that you want to select a single column (or more than one, but always with the same name and type) from all the tables. If you simply need all the data from every table, then the tables must have the same structure (the same column types in the same order, with the same names).

Notes:

• the format() function was introduced in version 9.1
• I've omitted some mandatory elements of any PL/pgSQL block to keep it simple
• the earlier version mentioned using a DO block. The problem with it is that you cannot simply use SELECT there to return rows, as it was pointed out in another answer.

• Al-Mothafar: Actually by omitting some mandatory elements, you ruin your answer as it is not helpful for a developer who got only a limited time for kick-in solution and no time to learn full course for this.
• András Váczi: You will find it in the other answer. However, you are not supposed to run anything copied from the interwebs without understanding it first, including anything I wrote :D
• Al-Mothafar: Well, I know that I'm not supposed to copy/paste anything, but I do understand things at least, so declaring variables should be included, and how you managed to get rid of first UNION, from my side I did something like dynamic_query := substring(dynamic_query, 7) || ';'; now EXECUTE dynamic_query not returning anything, I think I'm going to create function with table as return type and use insert for it, I'll share my answer once I get this done ;)
• Al-Mothafar: My answer is here if you like to review it, thanks :)

Answer 2

This is a workaround based on the above suggested approach.

 DECLARE
 tables CURSOR FOR SELECT * 
 FROM information_schema.tables 
 WHERE table_schema = 'public' 
 ORDER BY "table_name" ASC
 LIMIT ((SELECT count(*)
 FROM information_schema.tables
 WHERE table_schema = 'public')-1);
 --Because the following prepared string starts with a 'UNION ALL',
 --this completes the query string with a select starting with the last table
 sql_string text := 'SELECT field1, field7, field8, "source" FROM ' || quote_IDENT((SELECT "table_name" FROM information_schema.tables WHERE table_schema = 'public' ORDER BY "table_name" DESC LIMIT 1));
 BEGIN
 FOR table_record IN tables LOOP
 sql_string := sql_string || '
 UNION ALL
 SELECT columns FROM ' || quote_IDENT(table_record."table_name");
 END LOOP;
 sql_string := sql_string||';';
 RETURN sql_string;
 --EXECUTE sql_string;
 END;

Answer 3

OK, it is really late, but got the same problem to solve and I managed to solve it and return all rows in all tables under a specific schema using a FUNCTION as DO was not working for me, sadly suggested answers were not really working, as using DO was not returning anything, here is my FUNCTION:

CREATE OR REPLACE FUNCTION union_all_tables()
 RETURNS TABLE
 (
 id integer,
 full_name varchar(100),
 col3 varchar(200),
 col4 numeric,
 col5 char,
 version bigint
 ) AS
$$
DECLARE
 dynamic_query text = '';
 r_row record;
BEGIN
 FOR r_row IN SELECT table_schema || '.' || table_name qualified_table_name
 FROM information_schema.tables
 WHERE table_schema = 'staging'
 AND table_name LIKE '%_postfix'
 LOOP
 dynamic_query := dynamic_query || format('UNION SELECT ' ||
 'id, ' ||
 'full_name, ' ||
 'col3, ' ||
 'col4, ' ||
 'col5, ' ||
 'version ' ||
 'FROM %s ', r_row.qualified_table_name) || E'\n'; -- adding new line for pretty print, it is not necessary
 END LOOP;
 -- before we execute the query, we need to remove first "UNION " from the string
 dynamic_query := SUBSTRING(dynamic_query, 7) || ';';
 -- printing the statement as a notice so you know that the statement is in a good format, 
 -- or you can copy paste it and try it in the console to know if that statement is working or not. 
 RAISE NOTICE 'Union all tables in staging, executing statement: %', dynamic_query;
 RETURN QUERY EXECUTE dynamic_query;
END;
$$
 LANGUAGE plpgsql;

Note that I added AND table_name LIKE '%_postfix' here if you got some naming convention and you want to save some headache for that, if you don't want it, remove it, also note that if you want to make UNION all tables should follow the same structure, if not you select the common columns you want (and this is my case here in my solution), otherwise if you want all of the columns, it needs some more complex script for it.

This solution working for me in PostgreSQL 10 and 11.