CODEWITHSUNDEEP
sqlpostgresqlschema
nandeesh
nandeesh

Reputation: 823

Postgresql - Union table values from different schemas in a database into one table

Table details

I have a Database called Knowledge in postgres. It has multiple schemas and every schema has same number of tables, table has same columns as well. Now I want to create a new schema called Aggregate, table called aggregate.table1 and put values from schema1.table1 and schema2.table1 in it.

Question, Is it possible in Postgresql? if so please help me with this. I need this aggregated table for further processing

Upvotes: 1

Views: 747

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

You can try writing an anonymous code block to iterate over all schemas and tables, so that you can import your data into the aggregate schema. The following block search for all tables contained in the schemas s1 and s2, creates a corresponding table in the schema s_agg and finally copies its records.

DO $$
DECLARE row record;
BEGIN
  FOR row IN SELECT * FROM pg_tables WHERE schemaname IN ('s1','s2') LOOP
    EXECUTE 'CREATE TABLE IF NOT EXISTS s_agg.'||quote_ident(row.tablename)||
      ' AS TABLE ' || quote_ident(row.schemaname)||'.'|| quote_ident(row.tablename) || 
      ' WITH NO DATA;';
    EXECUTE 'INSERT INTO s_agg.' || quote_ident(row.tablename) 
     || ' SELECT * FROM '||quote_ident(row.schemaname)||'.'||quote_ident(row.tablename);
 END LOOP;
END;
$$;

Demo

CREATE SCHEMA s1;
CREATE SCHEMA s2;
CREATE SCHEMA s_agg;

CREATE TABLE s1.t1 (id int);
INSERT INTO s1.t1 VALUES (1);

CREATE TABLE s2.t1 (id int);
INSERT INTO s2.t1 VALUES (42);
        
DO $$
DECLARE row record;
BEGIN
  FOR row IN SELECT * FROM pg_tables WHERE schemaname IN ('s1','s2') LOOP
    EXECUTE 'CREATE TABLE IF NOT EXISTS s_agg.'||quote_ident(row.tablename)||
      ' AS TABLE ' || quote_ident(row.schemaname)||'.'|| quote_ident(row.tablename) || 
      ' WITH NO DATA;';
    EXECUTE 'INSERT INTO s_agg.' || quote_ident(row.tablename) 
     || ' SELECT * FROM '||quote_ident(row.schemaname)||'.'||quote_ident(row.tablename);
 END LOOP;
END;
$$;

-- contains values of t1 from s1 and s2
SELECT * FROM s_agg.t1;

 id 
----
  1
 42

Note: This code works with the assumption that the aggregate schema is either empty or it has empty tables, otherwise data will be duplicated. If you run this periodically and the size of your tables isn't too large, you can add a DROP TABLE before the CREATE TABLE statement. To make it work on every commit on all tables of all schemas you have to take a look at TRIGGERS or even logical replication.

Upvotes: 1

Related Questions