Take the 2-minute tour ×
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.

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
add comment

2 Answers 2

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)
as
$body$
declare
   full_query text := '';
   table_rec  record;
   row_nr     integer := 1;
begin
   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
   loop
     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;
end;
$body$
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 at 13:00
    
@dd_a see my edit –  a_horse_with_no_name Jul 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 at 14:16
add comment

This can be considerably simpler and faster:

DO
$do$
BEGIN
EXECUTE (
   SELECT E'CREATE TABLE t_new AS\n'
          || 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
   );
END
$do$;

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

CREATE TABLE t_new AS
SELECT tableoid::regclass AS source_table, * FROM t_20140707
UNION ALL
SELECT tableoid::regclass AS source_table, * FROM t_20140708
UNION ALL
SELECT tableoid::regclass AS source_table, * FROM t_20140709
...

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

Explain

  • 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 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/… –  Erwin Brandstetter Jul 14 at 15:45
add comment

Your Answer

 
discard

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.