9

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.

2
  • You must define exactly what to select. You cannot retrieve incompatible row structures in a single select. Dec 23, 2014 at 18:10
  • 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/…
    – Hubbitus
    Mar 4, 2021 at 15:56

3 Answers 3

5

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.
4
  • 1
    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. Mar 11, 2019 at 14:47
  • 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 Mar 11, 2019 at 15:35
  • 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 ;) Mar 11, 2019 at 15:49
  • My answer is here if you like to review it, thanks :) Mar 11, 2019 at 16:28
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;
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.