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.

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.

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.