I have received hundreds of tables in PostgreSQL with identical structure and each containing similar data of a different day, so that the table name is the date of the data.

I would like to Union them all into a single table but without losing that very critical information that is the date of the data. I guess that I should somehow use information_schema.tables and make use of PL/pgsql which I don't know and therefore should learn, but to be honest this seems to be a big effort for a one-shot operation. So, my question would be:
Is there an easy way to achieve this or what would be the best solution?

2 Answers 2


If you want to have some magic which does this automatically, you can use a function that dynamically creates a select statement and returns a union of all tables:

create or replace function get_all()
  returns table (data_source text, id integer)
   full_query text := '';
   table_rec  record;
   row_nr     integer := 1;
   for table_rec in select table_name 
                    from information_schema.tables 
                    where table_schema = 'public'     -- adjust here for your schema
                      and table_name like 'table_%'   -- adjust here for your prefix
                    order by table_name
     if row_nr > 1 then  
        full_query := full_query || ' union all ';
     end if;
     full_query := full_query || ' select '||quote_literal(table_rec.table_name)::text||' as data_source, t.id FROM '||quote_ident(table_rec.table_name)::text||' as t';
     row_nr := row_nr + 1;
   end loop;
   return query execute full_query;
language plpgsql;

The above function can then be used like this:

select *
from get_all();

And you'll get something like this:

data_source         id
table_2014-07_01    ..
table_2014-07_01    ..
table_2014-07_02    ...
table_2014-07_02    ...

If you want a different value for the "data_source" column, just do some pattern matching/replacing inside the function.

Another option would be to change the function to (re)create a view (instead of directly returning the data). That could make retrieving a bit faster.

You can also (re)create a materialized view using dynamic SQL in order to make the retrieval faster (because the result is then present in a single "table" that can be indexed properly).

  • Well, my problem is doing this automatically, because I have too many tables, and I will keep receiving new ones.
    – dd_a
    Commented Jul 14, 2014 at 13:00
  • @dd_a see my edit
    – user1822
    Commented Jul 14, 2014 at 13:07
  • a_horse_with_no_name, this is absolutly great ! I adapted it to have some more columns returned, and it works perfectly on my sample. Thank you very much !
    – dd_a
    Commented Jul 14, 2014 at 14:16

This can be considerably simpler and faster:

          || string_agg(format('SELECT tableoid::regclass AS source_table, * FROM %s'
                              , c.oid::regclass::text)
                       ,E'\nUNION ALL\n' ORDER BY c.oid::regclass::text)
   FROM   pg_namespace n
   JOIN   pg_class c ON c.relnamespace = n.oid
   WHERE  n.nspname = 'public'  -- your schema here
   AND    c.relkind = 'r'       -- only tables
   AND    c.relname LIKE 't_%'  -- your table pattern here

Replace * with your desired columns. Produces and executes a query of this form once:

SELECT tableoid::regclass AS source_table, * FROM t_20140707
SELECT tableoid::regclass AS source_table, * FROM t_20140708
SELECT tableoid::regclass AS source_table, * FROM t_20140709

So you get one new table containing everything plus the source table name.


  • A DO statement is a good choice for a one-time operation. You cannot return values from it, but you can create a new table. You may want to use a TEMP TABLE ...

  • tableoid::regclass works to retrieve the (properly escaped) table name of the source table and is very handy in various situations. More details:
    Get all partition names for a table

  • I build a query in a single meta-query instead of looping, which is almost always faster and cleaner (if possible). Related answer:
    Loop through like tables in a schema

  • Queries on catalog tables are regularly much faster and offer everything Postgres actually has. For instance the system column oid I am using here. Aside from that, there are pros and cons for using the information schema. Mostly it is useful to achieve some portability across major versions and various RDBMS. Portability doesn't seem relevant for a one-time operation and it hardly ever works properly in my experience anyway. More on that:
    How to check if a table exists in a given schema

  • Nice trick with the string_agg(). Inheritance won't help here I think as you can't add the column identifying the "source table" when using inheritance without changing the structure of the tables.
    – user1822
    Commented Jul 14, 2014 at 15:18
  • @a_horse_with_no_name: Well, I did not suggest inheritance, but I think this would work fine with inherited tables. tableoid is a system column, you don't have to add it. Consider this related answer: stackoverflow.com/questions/20575610/… Commented Jul 14, 2014 at 15:45

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.