Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 2 down vote accepted

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, 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).

share|improve this answer
Well, my problem is doing this automatically, because I have too many tables, and I will keep receiving new ones. – dd_a Jul 14 '14 at 13:00
@dd_a see my edit – a_horse_with_no_name Jul 14 '14 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 Jul 14 '14 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

share|improve this answer
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. – a_horse_with_no_name Jul 14 '14 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:… – Erwin Brandstetter Jul 14 '14 at 15:45

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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